What you want to do is possible. However, I would first ask if it's necessary. There are only a few circumstances when echoing data from a parent table to a join table is required. You can usually get by with the field from the parent table.
What's your goal for echoing the data? Maybe we can achieve it another way.
It is - thats why im doing it.
Users will join 'groups'. The groups have names. The names will show in the portals of the users as to what groups they are members of. But over time there may be a need to change the name of the group and would need the portal to show that change in name. (history has proved these groups mature and then change in name to show the changes in the groups and the focus).
I hope that helps explain it a little better. thanks for the help,
You can put the name of the group from the parent table in a portal that points to the join table. The field doesn't have to be in the join table to do that.
Unless it is recorded in another place, overwriting that field in the join table will erase the "name history" of a group. If – as Mike suggested – you simply display the name, then you have the same problem, and you don't even need an update script …
Think of invoice line items and product prices, which are snapshots; a price change shouldn't modify historical data, that's why you set the line item price as auto-enter and don't update it when you change the price. (And unless you have a price table, the line items table is the only place to track the development of your prices.)
An alternative – if you're interested in preserving "historical" names – is to have a group name table and set one of those names (or the only one) as default name for … well, whatever your entity is; auto-enter the name table primary key into your join table and display/access the name via this key. When you change the name, new join table records will auto-enter the new key, but old one won't change, and each record will have the correct "group name" as of the moment it was created.
Thank you for your reply.
When i do this (just add a merge field) I get '<Index Missing>' in its place. Same if i just add a normal field too.
What could i be missing? Its a pretty basic set up im testing and have not been able to make this work as you have explained. That would be exactly what im looking for if it did work that way.
Yes, if you want to record an actual "history" of what the group names were over time, then you'll need a separate table to do that. But I didn't get the sense that was what you were trying to do. Is it?
Personally i dont feel the data needs to be in the join table. But i cant see a way to add any other data from other tables to the portal without it in the join table. (that is what i first tried and did again after mike suggested it was to use a merge field linking back to the Parent table with the changing data on it). Maybe i am missing something - it sounds like i am. What could i be doing with my relationships that would not allow the parent data to show in the portal of the other parent table?
Im looking for the tech solution. The user culture is what it is. The name of the group changes over time as needed but much of the staff stay the same. A year or more may pass and the 'group name' field in parent may change. It will not be static and will be required in the portal for the users to see. The organization and staff involved have a process and the tech needs to follow. This is not a invoicing solution. It is a staff management solutions.
That is a good question on keeping a record of historical names and having a default name. I would think history of the fields would be kept in a separate database. Or ever have a second field that displays the previously named field for a more user friendly experience. But first i would like to get this basic need taken care of. My guess is that the rest will come together once it is.
Im pretty sure the fix for the problem will be simple and using the merge field from the parent table will be my answer once we figure out what could be blocking that in the portal. Thanks for all the quick help, you guys are great!
1 of 1 people found this helpful
Did you look at the example I posted?
Mike, I am pulling in a csv file from a radio station's airtime data. This table has records which I want to relate to the parent table, the value is basically a radios station's call letters which I want to be the referencial field to it's child data which is commercial end times going back into content (fwiw, to maximize the station's ratings ).
I look thru your project and I don't understand wherre you create a value for UUI, is it a global variable that I am missing?
I am simply trying to duplicate the call letters ( text) of the parent into a field of the same in the child so that this can become the (only) relational field.
I am simply trying to populate DaillyPPMDate table's CallLets field from a feild in the stations table upon import (from a CSV file)..
Am I missing something?
Nevermind on the UUID thing I see it in the list of get functions...
Thank you Mike for the example. I was pulled away and could not reply promptly. I do appreciate you quick replies.
My solution is set up the same as the your example. I can not figure out what could be wrong. the relationships are working, but the data does not show in the portal. frustrating. Maybe i need to rebuild this from scratch this and start from the beginning.
Your relationship between the tables isn't valid. When FileMaker says "<Index Missing>", it's telling you it can't index the field you're using for a key. That means it's one of the following:
1) A global field
2) An unstored calculation
You need to set the field to an actual, indexable field. Look again at my example, specifically at the key fields being used for the relationships.
Yes Mike - that was it. Thank you for that help and the very quick replies. I was just logging back in to share it also - I also figured it out when i saw in your example that indexing was turned on and not in my solution.
...So each field under the 'manage database' that needs to be shared in a portal needs to also be indexed.
What im not clear on is 'minimal' vs 'all' with indexing. Also, what is Automatically create indexes as needed. Digging in my books and manuals i have im not fully understanding why i would use one over the other.