Not only doe the records in Table 2 not update, but if there is a relationship that uses this field to link records in the two tables, changing the name disconnects the related records as they are now trying to match to a value that no longer exists. And don't forget that community names are not unique. It's quite possible to have two communities with the same name.
Don't link your tables by the name of the community. Use an auto-entered serial number to identify each company. Your Value list can list this ID field as the first field value and the community name field as the second field value so that you can still select communities by name. With the records linked by ID number, you can remove the Community name field from Table 2 and replace it with the community name field from Table 1. If the name of a community needs to be edited, this change will automatically appear on your layout for Table2 as it is showing the name from the same field.
The problem here is that every field representative is working in a copy of the filemaker database that only contains data about their communities. This for the reason that they not always have access to internet, so we can't opt for a server.
How could I then give their communities a serial number, if I merge all their information together in the complete database, once a week?
So but if I do can assure that all names of the communities in table 1 are unique, you say using this community field as input for the valuelist of communities for table 2 will be updated with every change I make in table1:communities ?
If you use an auto-entered calculation: Get ( UUID ) on a text field, each separate user can generate unique ID's in their copy of the database.
If your value list lists values from a table instead of a list of custom values, then any change to the data in a field that supplies data to the value list will automatically update the value list and if you set up a relationship to link tables by this ID field, A name field from the related table will automatically update.
Working with multiple copies of the same database can be a real challenge when it comes time to merge the data back into one database if that's something that you need to do. You may want to evaluate the third party produced "synch" tools available from such groups as SeedCode and 360Works for possible use in your database system.
Even though I follow your instructions, I don't get the community name in Table2 updated. This is what I have done: Table1:CommunityName => text, not allow user to override during data entry, unique value Value List:Community => Source: from field; Values: use values from field Table1:CommunityName Table2:CommunityName => Text, not allow user to override during data entre, unique value, member of value list: Community => field in layout = Table2:CommunityName; Inspector: Control Style: Dropdownlist from value list:Community Hope you see what I have done wrong... Thanks!
There shouldn't be any community name field in Table2. Delete that field and replace with with the communityName field in Table1 on your layouts to table 2.
I replaced it as you said with Table1::CommunityName => Inspector: Control Style: Dropdownlist from value list:Community Now I can't even enter any value anymore in this field... Nothing appears. What could I have done wrong?
And how can I then still relate both tables if I delete the Community name in Table2? before the relationship was: Table1:Community = Table2:Community Table1:Field rep = Table2:Field rep (I allowed creation of records in both tables via this relationship and I selected for Table2:'delete related records in this table when a record is deleted in the other table') Hope I can solve this problem... Thanks!!
The community name field should not be set up as the field with the drop down list and it should not be used in the relatonship either.
You should set up this relationship:
Table1::__pkCommunityID = Table2::_fkCommunityID
__pkCommunityID would be the field that auto-enters either a serial number or Get ( UUID ). _fkCommunityID would be a field of the same type as the __pk field (Number for serial numbers and text for Get (UUID) ).
_fkCommunityID would be formatted as a drop down list. The first field in your value list setup would specify __pkCommunityID. The second field would specify Table1::CommunityName. You can hide the first field value so that you only see community names, but the value list enters an ID to link the Table2 record to the selected Table1 record. If you place the Table1::CommunityName field on your layout, it will update to show the community name field after you select a value in the _fkCommunityID field. Or you can set up the _fkCommunityID field as a pop up menu and then the community name will always show in this field as long as there is a matching record with that name in Table1.
I did all the steps you asked me to do. As the communities in table1 didn't have a serial number yet, I assigned on to each via 'Replace field contents'. Everything ok till then.
The problem I now encounter is that in table2, the serial number is not automatically assigned, and thus it doesn't recognize the communities. When I create a new community in table1, it does work, so the right serial number and community appear in table2.
If I try to 'replace field contents' of _fkCommunityID; these serial numbers are not the same as _pkCommunityID although the relationship is set up as Table1::_pkCommunityID = Table2::_fkCommunityID
Thanks for helping me out again... it is so well appreciated!
Maybe I should just assign the unique text (Get(UUID) to all the existing records in Table2, manually? But as there are 190 records, I thought there would be an easier way..
The new records I create as new in Table2 are linked correctly and automatically with the communities from Table1
Replace field contents can assign Get ( UUID ) to a field of every record in your table in one go.
Yes I know, and this works perfectly well for the serial number of Table1. But if I do the same for _fkCommunityID , I do get assigned a serial number to all the communities, but it e.g. CommunityA in table1 does not have the same serial number as CommunityB in table2; although the relationship is defined that Table1:_pkCommunity = Table2:_fkCommunity Only the communities I create right now, have the same serial number in Table1 and Table2
Ok. Replace Field Contents can be used there as well if you set things up for it in advance. This issue has popped up before in the forum where a person has imported a bunch of records and needs to link them by an ID that is defined in FileMaker--not the file from which the data is imported.
The trick is to set up two relationships to the same file. One relationship matches by name and the other by ID so you use the "name match" to set up the ID match for future use. To get two different relationships between two tables, you select the Tutorial: What are Table Occurrences? for one in Manage | Database | Relationships and click the duplicate button (two green plus signs) to make a duplicate occurrence.
Table2|ByName::CommunityName = Table1::CommunityName
Table2|ByID::__fkCommunityID = Table1::__pkCommunityID
Then you can show all records on a layout based on Table2|ByName and use Replace Field contents with the Calculation option and this expression:
to copy that value into the Table2|ByID::_fkCommunityID field