Newbie Problem Displaying Linked Records

[MongoZen]

First time using FM Pro. I have an equipment, package and package link table. Equipment is list of equipment, package is list of equipment packages i have and package link table allows one package record to assocaite with multiple equip. records.

I want to create a form that lists all the equipment in a single package, however I can only get a form to show the package and then each piece of equipment one at a time. Using a portal but can't figure out how to simply have the form display ALL of the equipment in a list on one form and the use the package name field to select which package to display.

Tried to attach fm file, but wouldn't let me. I have included screen grab of relationships screen.

[comment]

Can one piece of equipment be in two (or more) packages?

[MongoZen]

Can one piece of equipment be in two (or more) packages?

No. Equipment cannot be in more then one package.

[comment]

Then what's the purpose of the third table? It seems like a simple parent-child arrangement.

[MongoZen]

Then what's the purpose of the third table? It seems like a simple parent-child arrangement.

OK. I have been trying to get the two tables to talk without the equipment table creating multiple copies of the packages. So I want one record called 7D Camera Package in the packages table and then link several equipment records to that one record. It doesn't seem to want to do that.

I just tried it and I have attached screen shots of what happened, the first is the layout of the form (you can see the portal below the basic info) and then table view of the same data where the database now has several repeated records of 7D Package linked to in piece of equipment each. I have also included what the relationships area looks like now and the "Edit Relationships" window for the relationship between the Equipment and Packages tables.

I can get one record in one table to relate to one record in another table no problem, the trouble is when I try to relate several records in one table to one record in another table it falls apart. In the end I want the ability to add equipment, assign pieces of equipment to packages and then display, in a form, the package name with all the equipment associated to it below. I know I am missing some simple concept.

[comment]

I am not sure I follow your description. If you own one 7D Camera Package, then there should be one "7D Camera Package" record in the Packages table - not 14 or whatever you have now. If that record has a value of "2" in the PackageID field (in the Packages table), then any equipment that goes into that package needs to have "2" entered into the PackageID field (in the Equipment table). That's how they become related.

[MongoZen]

I am not sure I follow your description. If you own one 7D Camera Package, then there should be one "7D Camera Package" record in the Packages table - not 14 or whatever you have now. If that record has a value of "2" in the PackageID field (in the Packages table), then any equipment that goes into that package needs to have "2" entered into the PackageID field (in the Equipment table). That's how they become related.

Exactly, now you see why I am confused. When I create a form with the Package Name at the top and a portal to the equipment table below and start cycling thru the records I start seeing the equipment but only listed one at a time (EXAMPLE: I will see each of the four batteries for the 7D package as a seperate record). When I stop browsing thru the records and go and look at the information in table view I see that a bunch of new records (as you have seen in my screen grab) have been created. Before I do all of this I only see one package per record in the Packages table.

I created a few of the packages while entering equipment info (screen grabs of that form attached) with no problems, I tried to help myself by making the equipment package list a drop down. This was working great and I loved it.... and you know the rest.

I just attached a zip file of the dbase if you want to take a look.

Thanks for being patient and answering my questions.

Cambio Equipment Database 2.fp7.zip

[comment]

I think you may be misled by viewing the equipment through a table view of the Packages table. In this view, only the first related equipment record will show. Though I am not sure how you managed to create that many duplicates.

See if the attached helps.

Cambio Equipment Database 3.zip

[MongoZen]

I think you may be misled by viewing the equipment through a table view of the Packages table. In this view, only the first related equipment record will show. Though I am not sure how you managed to create that many duplicates.

See if the attached helps.

Attached file helped so much. I figured out what the problem was. My original dbase did not have ID#'s associated with the original list of packages. When assigned them to a package there was no way to link it, but I had a value for the package name. So when I started cycling thru the records from the POV of the packages table it created a record for every instance where the package name had a value, thusly a repeated record for every package.

... or at least I think that is what happened. I have took a look at what you built, compared to my original and spotted the missing data. I then rebuilt it making sure that each package had an id number and it is working fine so far.

Thank you, thank you, thank you. I am sure I will have more questions but until then I hope I have the luck to attract the guidance of someone like you again.

[comment]

it created a record for every instance where the package name had a value, thusly a repeated record for every package.

... or at least I think that is what happened.

You should check your original file's relationship definition for "Allow creation of records...". You definitely do not want to allow creation of new packages via the relationship - and since I assume you'll be creating a new equipment record BEFORE assigning it to a package, you wouldn't want to allow the creation of records on that side either.

[MongoZen]

You should check your original file's relationship definition for "Allow creation of records...". You definitely do not want to allow creation of new packages via the relationship - and since I assume you'll be creating a new equipment record BEFORE assigning it to a package, you wouldn't want to allow the creation of records on that side either.

Thanks for the advice, will do.