Populating related fields

[sean o mac]

I keep running around in circles on this one and I get close only to find I can't do what I want to. I hope someone can assist me fix what seems like a simple problem.

I have related tables, one called Events, the other called Press. I believe it can be considered a many to many relationship since one Event can have many Press contacts and all Press contacts can be related to more than one Event. Thing is, I have only used it in one direction - namely one Event to many Press contacts.

In the old days I used to use a silly relationship in FM6 that we won't even talk about, though all records in both files have always had Unique Auto-Entered IDs (whew!). I have now created a much more sensible relationship based on fk_press ID (in Events) and pk_press ID (in Press), and if I type into the fk_press ID field it will populate the related fields. No problem there.

...however...

What I need to do is base it on a filtered value list since I have hundreds of records in the Press table. At the moment I have created a value list based on a second relationship relating the two tables by Country. Clearly, the list is displaying names and not record IDs. So far so good. What I CAN do is choose the appropriate record from this filtered list, but I CAN'T figure out how to populate the rest of the related fields connected to this action, similar to when I type into the fk_Press ID field.

Make sense? It would seem to me that this would be relatively easy to do but I can't get my head around it. I keep getting close then getting completely stumped. I would attach the files in question for you to look at, however I have greatly simplified my question to make it easy to undersand what I am trying to do. Unfortunately this is not a small file I am trying to work on so would be impossible to upload.

Please let me know if you can suggest what I am doing wrong. Also, if you can toss me a tiny file displaying this feature I would be greatly appreciative.

thanks for your time!

[Søren Dyhr]

If I get what you say correctly do you need a way to get entire found sets without taking the plunge into genuine recursive relational structures dealing with a mixture of sets/kits and single items???

Investigate this: http://www.dwdataconcepts.com/dl/tw/compinv2.ZIP

--sd

[Ender]

I have related tables, one called Events, the other called Press. I believe it can be considered a many to many relationship since one Event can have many Press contacts and all Press contacts can be related to more than one Event. Thing is, I have only used it in one direction - namely one Event to many Press contacts.

The best way to structure this many-to-many is with a Press table, an Event tables, and an Attendee (Press-Event) join table.

The data entry can be done in a portal of Attendee on a layout based either on Event or Press (or both), depending on how the enrollment process works. If you like selecting the Press for a specified Event, then do it from the Event table.

If you're using a value list for selection, you should really stick with IDs. You can always show the related Name or whatever from the related table.

[Ender]

I think you're slipping Soren.

If I get what you say correctly do you need a way to get entire found sets without taking the plunge into genuine recursive relational structures dealing with a mixture of sets/kits and single items???

Investigate this: http://www.dwdataconcepts.com/dl/tw/compinv2.ZIP

What the heck is this sample supposed to show??

[Søren Dyhr]

I am indeed on slippery ground here, what is tough to grasp is what this means:

but I CAN'T figure out how to populate the rest of the related fields connected to this action

To my defense could it be said that your:

Press table, an Event tables, and an Attendee (Press-Event) join table

Is quite similar to an Invoice table, products table and the invitem jointable, the templates deal with how to facilitate sets from some of the storageID's eventhough the valuelist as such is ditched.

Populate is one of my "Yellow flares" words as LaRetta call them.

--sd

[sean o mac]

I used the term 'populate' because someone else did for what I presumed to be a similar action. Possibly its not a very good description and is out of bounds for correct FM terminology. Apologies if it is.

Let me reply to the couple of comments above. First of all, I can see similarities between Events/Press/Attendees and the Invoice/Products/inventory formats so you aren't that far off. But is that solution more complicated than it needs to be? Maybe not, and I will review the file to see if it can help me, however...

What I meant by 'populate' is this: if you were to type in the correct number to the fk_ID field, then the rest of the fields on the layout that are related to that ID number are obviously filled in. Simple, no? I just want to do the same thing without typing in the ID numbers - I want to choose the related record from a value list of Contact Names, the names also filtered by the country the event is in (to keep the list shorter).

Imagine this from the point of view of someone not used to FM Pro. It strikes me as inelegant to type in an ID number when you can choose a name from a drop down list that you likely recognize. Its the way most of us work on the web these days and I am always thinking about the end user first and foremost. I had a workaround I used for years that was simply poor FM structure: the matchfields were the Contact Names, not the ID numbers, and I simply wanted to fix this. What amazes me is how challenging it it to both get this idea across to FM Developers and (maybe) even to accomplish. Please let me know if you have any suggestions - presuming I have made question clearer of course! ha ha

[Ender]

In the value list definition you can choose to show a second field, and can now show only the second field. Use this feature to have the first field be the ID, and the second field be the Name, then show only the Name.

If the ID is selected, you can establish relationships to other tables for showing any other related values or populating local lookups.

[sean o mac]

In the value list definition you can choose to show a second field, and can now show only the second field. Use this feature to have the first field be the ID, and the second field be the Name, then show only the Name.

If the ID is selected, you can establish relationships to other tables for showing any other related values or populating local lookups.

I tried that and couldn't make it work, which was my original solution. Possibly my problem was that the field I was doing this with wasn't the fk_ID field but the Contact Name field? Possibly I was reversing what I needed to do.

However, if I understand you correctly your solution will still look a little funny to the endUser as they will pull the selection from the list of Contact Names, but the ID number will appear when the make the selection. Regardless, if it works it will still be functional. I will attempt this again and see if I can get it to work as suggested.

thanks

[Søren Dyhr]

It strikes me as inelegant to type in an ID number when you can choose a name from a drop down list that you likely recognize.

Enders suggestions should work, just tinker a little more with it!

However might other solutions suit your purpose better, and give your users a pretty intuitive interface:

http://www.dwdataconcepts.com/dl/tw/Many2Many.zip

...when you have considered it as a solution, beware that the tampering with the clipboard it does ...is way to naughty to pass without proper precuations, the easiest is to save the clipboard with the scripting and restore it, but it's clunky - a serious stab in this direction will include a CF to strain/increase the multiline key acting as jointable ...say:

http://www.briandunning.com/cf/39

--sd

[djlane]

So that your users see the contact name instesad of the ID, do this

Make the field width for the ID just wide enough to show only the dropdown arrow (19 pixels works for me). Place this immediately to the right of the Contact name field (disable the contact name in browse mode). It will look like a dropdown for the customer nsme field. When you select from the list as per Enders instructions, your users will see only the contact name.

[sean o mac]

Now THAT is a good idea. I will try it and post the results.

thanks!

[sean o mac]

Soren, I really appreciate that you are sending possible solutions to me. What am curious about is why you are sending solutions created in FM3/4? Although the theories behind relational databases haven't changed since moving to FM7/8, the implementation certainly has. In addition, if you were to consider your solutions to be a way for me to understand something I might be missing in basic relational DB theory (entirely possible), wouldn't you agree that even the way FM8 represents relationships graphically to be easier to understand? Your profile says you are working with FM8.5 advanced (like myself), so just curious why you are using examples from such an old FM platform?

[Søren Dyhr]

Primarily because the interface is pretty intuitive, while indeed the methodology is changed considerable since those days. If you pick up Hernandez "Databases for mere mortals" should the legacy base be part of the planning ...now i know it isn't your previous system, but then the filemaker legacy ...but the approach Don Wieland shows, is in my humble opinion somewhat better than popups or dropdowns.

wouldn't you agree that even the way FM8 represents relationships graphically to be easier to understand?

I would, but if you investigate the files after being migrated, shouldn't it be too complicated to merge one of the tables into the other ...in order to bring up to present methods. Many 2 many relations sans join tables, however isn't gone quite out of fasion yet ...it was this kind of functionality I tried to convey.

I know I once made an update of Dons Files, and uploaded it here ...but tracing my file uploads in this forum isn't the easiest.

--sd

[sean o mac]

OK, I did try reworking the value lists and fields as suggested above and it worked, though not with results I expected.

By using the fk_eventID field where you have the drop down menu, using a value list based on a second relationship filtering by country and hitting the button that says 'only see related values from the Event file', I got the filtered list I wanted plus the related fields DO automatically fill themselves in. So great, that works now.

What suprises me is that the fk field displays the company name and not the fk_eventID as expected. On the one hand, if it works then don't fix it. On the other hand, why would the relationship be working at all between fk_eventID and pk_eventID if the value placed in the fk field has anything other than an ID number in it?

Is this because I can assume that the VALUE in the fk_eventID field is the ID number but what I am seeing is the value from another field? Is this a quirk in FM8 that allows for easier solution building? If so, that would be a great example of where my question to Soren above is highly relevant - namely that FM8 implementation is so drastically different than previous versions that (notwithstanding his valuable comment about the benefits of the many/many relationships he is discussing) its challenging to discusss implementation questions using older files. Please explain this one to me if you can so I know I am not crazy.

Edited April 14, 2007 by Guest

[Søren Dyhr]

If you investigate the migration I've made, hardly made any changes to the relational structure, except for the unequal type of relation and the riddance of the global field. Strictly speaking isn't there much point in having two keyfields, but I've kept them for illustrational purposes.

While on the scripting side of the matter, have the solution benefitted from much more sophisticated scripting options, the use of List( is much more convenient than building dynamic valuelists and by it the use of ValueListItems( further more is the command nice to use instead of measures to remove excesses of ¶ (pilcrows)

--sd

DWLegacy.zip

[Søren Dyhr]

I've just realized that I forgotten to make the key pointing at availiable an autoenter of ~¶ ...since it's working as a strainer key, should a newly created record point at the entire range of records until the first choise is made:

--sd

DWLegacyFix.zip

[Søren Dyhr]

My version of FM9 have arrived yesterday, and I have too invisioned the changes the unequal relation have recieved. In my humble opinion is it crippled compared to what it used to be with fm7 thru fm8.5, you can't any more use an arbitrary chosen char such as ~¶ to show all, a genuine found value from the foreign side is required to sift it out of the related records found set.

And yet another template is then urged:

http://fmforums.com/forum/showpost.php?post/265338/

--sd

[comment]

a genuine found value from the foreign side is required

I am not sure that is a good way to put it. AFAICT, it's a type issue: version 9 is less tolerant of a type mismatch between match fields. A space or a tilde still works with a ≠ relational operator, but only if both fields are Text.

Since most of us tend to define SerialID as a Number, you need to append a zero or minus one to the list to make a dwindling value list work in v.9.

[Søren Dyhr]

AFAICT, it's a type issue

What a relief! Thank you...

--sd

[bcooney]

btw, they can accidentally type (not select an id) in a drop down list.