• Registered: 2015-11-16
  • Posts: 158

Topic: ComboBox used twice on a form and a Table Grid

Hello,
I have an application where a dispatcher gets one of the staff to do a delivery from one location to another. I have a table which contains the job details and another table with the locations the delivery is from and to. The dispatcher enters the job in the form and the "From" and "To" fields are both combo boxes which use the entries in the locations table. When the record is saved I then would like to show the "To" and "From" fields in the Table Grid but it seems to get confused because I want different values in both of the fields but the software makes them both the same. I use the same combo box lookup table for both fields because the deliveries go in both directions with these locations.
Can someone help a beginner please.
Thanks in advance, David

  • Registered: 2014-02-14
  • Posts: 10,294

Re: ComboBox used twice on a form and a Table Grid

Hello,


For second foreign key you should use calculate field to display its value in a TableGrid, I  made an example for you:

Post's attachments

Attachment icon Two comboboxes.zip 14.71 kb, 636 downloads since 2016-02-08 

Dmitry.
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: ComboBox used twice on a form and a Table Grid

Hi David,
You need to define the relationship between your 'jobs' table and your 'locations' table twice (once for the 'from' locations and once for the 'to' locations).
This however, will only display the internal keys values of the 'from' and 'to' locations on your tablegrid so you need to also add calculated fields to bring in the actual location descriptions (you actually only need to get the 'to' location description - the standard MVD software will get you the 'from' location description - I've just done both to make it a bit easier to follow).
Oh - just seen that Dmitry has already sent you the solution - anyway, here's what I knocked up (which is much the same).
Good Luck
Derek.

Post's attachments

Attachment icon radsoft dispatch.zip 337.24 kb, 538 downloads since 2016-02-08 

  • Registered: 2015-11-16
  • Posts: 158

Re: ComboBox used twice on a form and a Table Grid

Gentlemen,
thank you for the answers and going to the trouble of creating the demos. I have not encountered a forum like this before. Its awesome!
Regards, David.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: ComboBox used twice on a form and a Table Grid

David,
Just noticed that in my example, the code for the two calculated fields is incorrect (bad copy and paste!!).
The code for the 'from' description should be (select location from locations where id = jobs.id_locations) - the 1st link between jobs and locations.
The code for  the 'to' description should be (select location from locations where id = jobs.id_locations1) - the 2nd link between jobs & locations.
Previously, they both pointed to jobs.id_locations1.
Sorry about that.
The ability to set multiple relationships between the same 2 tables is pretty useful.  In your example, you could dispatch from Point 1, going via Point 2 and Point 3 arriving at Point 4 simply by creating 4 relationships, 4 calculated fields and then use the same combo-box lookup.
A different use is that you can create multiple relationships between the same two tables but store completely different entities in the one table and then restrict what the combo-box displays with a filter (think about it as creating lots of 'sub-tables' all within one 'holding' table);  the advantage is that you don't need to create additional tables, additional forms etc etc - it's a huge time-saver.
Derek.