Selectively updating two fields with a dblookupcombobox lazarus / 16-Databases ---------------------------------------------------------------------- percentage · 2013-11-13 22:35 I'm a beginner and I'm learning how to program with Lazarus as a hobby. I ran into a little trouble. I use SQLite, Zeos 7.1.2 and Lazarus 1.0.12. Say there are objects that can have one of two properties, color or shape. They are assigned in a table as FKs: IDobject | FK_IDcolor | FK_IDshape ------------------------------------  1       | 2          |  2       |            | 2  3       |            | 1  4       | 2          | What I want is to update the table with a dblookupcombobox that lists all the items from the color and shape tables (blue, green, triangle, circle, etc - all in the drop down list). Basically, if I click the dblookupcombobox and choose an item and it's a color, its ID should be written as a FK in FK_IDcolor, if it's a shape, its ID should be written as a FK in FK_IDshape. To fill the dblookupcombobox, I can make an union between the color and shape tables and get a list of all the colors and shapes along with their IDs, but I don't know what to do next. How do I put those IDs in either FK_IDcolor or FK_IDshape? ---------------------------------------------------------------------- kpeters58 · 2013-11-14 03:13 I would use a plain combobox because it gives me better control and load the descriptive strings into its Items property, so that  its dropdown list might read (if I have understood you correctly): BLUE GREEN CIRCLE TRIANGLE etc. For each of these stringlist items I would create an object (at load time) containing the key (integer) and column name (string) and attach it. Upon selection grab the key and column name and do your thing - done! ---------------------------------------------------------------------- taazz · 2013-11-14 05:41 Simply change the union sql to have one extra field with two values 0,1 with 0 coming from the color and 1 coming from the shapes and name it IDType. That will give you the flag to choose between color and shape you will need to leave the datafield empty and set it manually on selection or on closeup events, as far as I know there is not way to do that automatically with the dblookupcombo. At this point I'd like to say that I disagree with your way of doing things it is better to have two combo boxes one for color and one for shape it makes easier for the end user to understand and use. ---------------------------------------------------------------------- percentage · 2013-11-14 15:36 I can see how it would be easier, but in my application it makes more sense to do it this way. When I posted I also changed the data a bit to keep it short and clear. Quote from: taazz on November 14, 2013, 06:41:57 amSimply change the union sql to have one extra field with two values 0,1 with 0 coming from the color and 1 coming from the shapes and name it IDType. Done. Now I got this table: IDproperty | Property | IDType ------------------------------- 2          | green    | 0 2          | circle   | 1 1          | square   | 1 2          | green    | 0 "Property" is assigned to ListField and IDproperty to KeyField. IDType should work as a switch that swaps the DataField's value between FK_IDcolor and FK_IDshape when the OnCloseUp event is fired. I tried but I didn't find a way to do it. How do I go about it? A code example would be appreciated. ---------------------------------------------------------------------- taazz · 2013-11-14 16:31 There are a number of problems I can see on this 1) you have green twice 2) you have the same IDProperty for green and circle. 3) there is no way to enable the dblookupcombo to show what is currently selected at least not in its current implementation. On close up should work as long as the dataset is in edit mode at that time. I'll try to find some time to build a sample but I do not know when I'll find it. ---------------------------------------------------------------------- engkin · 2013-11-14 17:19 Why do you have two tables, one for colors and one for shapes? If I understood your problem right. Your "objects" can have either one color or one shape, but *NOT* both. One table for both colors and shapes should solve your problem. If you're just practicing then simply excuse my intrusion. ---------------------------------------------------------------------- taazz · 2013-11-14 18:00 wrong it has both color and shape ---------------------------------------------------------------------- engkin · 2013-11-14 21:29 Quote from: taazz on November 14, 2013, 07:00:25 pmwrong it has both color and shape That does not change much, I just wanted to clarify it. According to the original post: "objects that can have one of two properties"  then you could see the example: Code: [Select]there are objects that can have one of two properties, color or shape. They are assigned in a table as FKs: IDobject | FK_IDcolor | FK_IDshape ------------------------------------  1       | 2          |  2       |            | 2  3       |            | 1  4       | 2          | I don't see two FKs for the same object. Even if one object can have a color and a shape, that does not prevent having colors and shapes in one table. I'm just curious, why would he insist on having two separate tables while using one TDBLookupCombo, unless the OP is trying to practice and create such a challenge which is definitely solvable. ---------------------------------------------------------------------- percentage · 2013-11-15 00:13 Guys, thank you, but after trying to find a solution for a few hours I don't think it's possible to do what I want. Read on if it wasn't clear what I wanted to do. In my application color and shape is equivalent to "person" and "business". There are classes of objects that can be owned by either a person or a business, for example bank accounts. To associate a bank account with a name, I made the table "account" (which has more fields, but these are the ones relevant here): IDaccount| AccountName | FK_IDperson | FK_IDbusiness -----------------------------------------------------  1       | Bank Acc. 1 | 2           |  2       | Bank Acc. 2 |             | 2  3       | Bank Acc. 3 |             | 1  4       | Bank Acc. 4 | 2           | The person and business tables: IDperson | PersonName --------------------- 1        | Mary Pike 2        | John Doe IDbusiness | BusinessName ------------------------- 1          | Pixar, Inc. 2          | Honda On the form we have a TZQuery named ZQuery1. The SQL is: Code: [Select]SELECT IDaccount, -- For the TZUpdateSQL FK_IDperson, FK_IDbusiness, AccountName AS "Account Name", COALESCE(person.PersonName, business.BusinessName) AS "Owner Name" FROM account LEFT JOIN person ON account.FK_IDperson = person.IDperson LEFT JOIN business ON account.FK_IDbusiness = business.IDbusiness After we connect a grid to ZQuery1 and hide the FK and ID fields, it displays the following: Account Name     Owner Name Bank Acc. 1      John Doe Bank Acc. 2      Honda Bank Acc. 3      Pixar, Inc. Bank Acc. 4      John Doe I wanted the end user to update the Owner Name field by choosing from a dblookupcombobox that lists all the persons and businesses (colors and shapes). So the dblookupcombobox has to selectively update FK_IDperson or FK_IDbusiness from the table "account". taazz suggested to flag with 0 or 1 whether it's a person or a business - color or shape. Then the SQL to populate the dblookupcombobox is (ZQuery2): Code: [Select]SELECT person.IDperson AS IDowner, person.PersonName AS Owner, 0 AS IDtype FROM person UNION SELECT business.IDbusiness, business.BusinessName, 1 FROM business Visually it looks like this: IDowner | Owner       | IDType ------------------------------- 1       | Mary Pike   | 0 1       | Pixar, Inc. | 1 2       | Honda       | 1 2       | John Doe    | 0 In dblookupcombobox's properties we assign the following: ListSource: ZQuery2's Datasource ListField: Owner KeyField: IDowner DataSource: ZQuery1's Datasource So far so good. Now... The DataField must be either FK_IDperson or FK_IDbusiness, depending on the user's choice - if he chooses John Doe(person) or Honda(business). How do we know which is which? Well, we flagged whether it's a person or business with IDType. When IDType is 0, DataField should be FK_IDperson. When IDType is 1, DataField should be FK_IDbusiness. So we have to code that. But now I don't think the dblookupcombobox can work that way and I'll have to find another solution. Here is the project if someone wants to see it (needs Zeos, VirtualDBGrid and sqlite3.dll). ---------------------------------------------------------------------- taazz · 2013-11-15 07:14 I'll try to take a look this weekend but since there is no way for both person and business to be present on the same record then the change is simple. Assuming that the dblookup is an inline editor and not something always visible then create two queries one for persons and one for business just before showing the editor attach it to person query and the fkID_Person field or the business query and the fkID_Business based on the IDType you have already and let it do the job for you. If it is not a inline editor which has the advantage to be hidden when not editing then you have to do the same on the datasource onscroll event or something similar I don't recall the exact event I need to look it up. Let me add at this point that I have abolished the foolish notion of person-company or suppliers-customers in my applications for the last 10 years or so all those are flags on a contacts table which is the natural think in my opinion they are not different after all, people talk to you in all the cases (contacts) for different business reasons using different hats.