1 2 Previous Next 16 Replies Latest reply on Dec 19, 2013 3:06 PM by philmodjunk

    Update value list

    IlseVerhaert

      Title

      Update value list

      Post

           I have two tables that are related. A field 'Community' from table2 uses the values from the field 'Community' from table 1 as input.  

           My problem is that when I am making adjustments to the community name in Table 1, this community name in Table 2 does not get updated automatically. How can I change this, and thus make sure that the valuelist consisting of the Communities of table 1 gets automatically updated? 

           Thanks!

           Ilse

        • 1. Re: Update value list
          philmodjunk

               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.

          • 2. Re: Update value list
            IlseVerhaert

                 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?

                  

            • 3. Re: Update value list
              IlseVerhaert
              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 ?
              • 4. Re: Update value list
                philmodjunk

                     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.

                • 5. Re: Update value list
                  IlseVerhaert
                  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!
                  • 6. Re: Update value list
                    philmodjunk

                         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.

                    • 7. Re: Update value list
                      IlseVerhaert
                      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?
                      • 8. Re: Update value list
                        IlseVerhaert
                        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!!
                        • 9. Re: Update value list
                          philmodjunk

                               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.

                          • 10. Re: Update value list
                            IlseVerhaert

                                 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!

                            • 11. Re: Update value list
                              IlseVerhaert

                                   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

                                    

                              • 12. Re: Update value list
                                philmodjunk

                                     Replace field contents can assign Get ( UUID ) to a field of every record in your table in one go.

                                • 13. Re: Update value list
                                  IlseVerhaert
                                   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
                                  • 14. Re: Update value list
                                    philmodjunk

                                         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>----Table1-----<Table2|ByID

                                         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:

                                         Table1::__pkCommunityID

                                         to copy that value into the Table2|ByID::_fkCommunityID field

                                    1 2 Previous Next