Forums

Hello,

I'm working with two tables. Table 1 holds basic information that won't change. Table two holds information about table 1 entries that will change so we can keep a history of the changes.

So, say I have a VCR. Table 1 will contain for example, VCR, model, serial number etc. Table two shows it is located in bldg x, room x. Now, I move the location of the VCR to bldg y room y and update table 2 accordingly via an update form.

Now, I click on the menu item to display a list of equipment (using a Connection Data List) to display a list of equipment. In the connection I use the multitable tab to set up a relationship between tables 1 & 2. I do this because if you click on one of the items in the list, it brings up a form (selected in the Front editing tab) which displays more detailed information of the equipment (including a list of all the locations that item has been, which is gotten from table 2).

Now, there is only one entry for the VCR in table 1 but 2 entries in table 2. The problem I'm having is that I get two displays of the VCR in the initial equipment display list. How can I get the connection to display the item only once?

I've researched MSQL statements and found a "SELECT DISTINCT" but I'm not sure how to use this in ChronoConnectivity/Forms.

If you think this would solve the problem, how would I implement it? If not, how would I eliminate duplicate displays of a record?

Thanks so much for your help.

Hi chriso0258,

How do you know which is the 'right' entry to look up from Table 2?

Bob

How do you know which is the 'right' entry to look up from Table 2?

Hello Greyhead,

The form that's called from the Connectivity app is called equip_details. In the OnLoad event I have put a DB Multi Record Loader. The DB Field contains a variable called st_tag which is located in table 2. It is the foreign key from table one. In the Request Param box I have equipinfo.cf_id which is the primary key for table 1. After the list is displayed, when a user clicks on a tag number, that equipment's cf_id is passed to table 2 and all the records of where that equipment has been located is pulled up, giving us a history of it's location. This part is working great.

Where I'm having problems is the initial listing is showing the equipment twice.

[attachment=1]equip list.png[/attachment]

Notice that the cf_id (164) is the same for both entries as there is only one entry in table 1. I suppose that for some reason it's pulling up two displays due to two entries in table 2.

When a user clicks on M97126, they get more detailed information plus a listing of the locations as seen below.

[attachment=0]equip details.png[/attachment]

Notice that in this example, this piece of equipment was moved on the 20th and then the 28th. This part is working great.

Hope this explanation helps. Again, thanks for your help.

Chris

Hi Chris,

Sorry, I still have the same question. How do you know which of the two (or more records) in the second table is the correct one to link to the result from the first table?

This is the difference between an inner and an outer join in MySQL (or LEFT and RIGHT joins) Google those and you'll find some tutorials explaining the difference. The Multi-Tables Settings | Advanced tab has some setting where you can adjust this but I'd hesitate to try to describe them accurately.

Bob

Hi Greyhead,

Thanks for responding. I figured it out. It seems that what I'm doing (displaying the location history when the equip_details form is called) does not require me to set up a join between the two tables. I disabled the multi tables setting and things work fine. I get no duplicate records in the initial display and I still get the list of locations when the equip_details form is called (because of the DB Multi Record Loader I have in the OnLoad form event).

I will still look up those suggested joins as I need to learn more. Thanks for the suggestions and quick support.

Chris