1 2 Previous Next 20 Replies Latest reply on Aug 24, 2015 5:38 AM by g4guitar

    Automatic record updating


      I have a Customer table and a Timetable table and layouts to match. On the Timetable I have times and a dropdown list of Customers which comes from the Customer table field 'c_FullName'. This works fine except when I go back to the Customer and make a change (e.g. the spelling of their name) the Timetable does not update. What do I need to do to make the change occur on the Timetable if and when a change is made on the Customer table?

        • 1. Re: Automatic record updating
          Markus Schneider

          without changing much of the structure: Select the customer_ID in the popup and display the customer's name from the customer-table via relationship in a second field (the popup can show the customer' name as the second field, but enters the ID)

          You could also have a script in the customer table that -after changes- goes to the time-table, searches for the specific customer (via ID..) and updates the names


          Depending on other factors, there are other methods..

          • 2. Re: Automatic record updating

            when selecting a Customer in the Timetable layout... have the value list use values from the Customer ID field instead of the name of the Customer.

            • 3. Re: Automatic record updating

              Thank you so much. That's help me get to the next step. Now how do I get the display to show the customer's rather than the ID number?

              • 4. Re: Automatic record updating

                Instead of Dropdown list you can have 'Popup Menu' for the field.

                Another option would be,create a relationship from timetables customer_ID field to Customers customer_ID field table.

                Place the text field i.e. Customer name from this relationship over the Dropdown list. so after you select from the dropdown the name will be displayed and not the ID.

                • 5. Re: Automatic record updating

                  shwetam@metasyssoftware.com wrote:

                  Another option would be,create a relationship from timetables customer_ID field to Customers customer_ID field table.

                  That would be a must, rather than “another option”, if you want to relate these tables properly.


                  You can use a value list to insert an ID and see the associated name (in a popup control) even without a relationship, but there must be a reason why you wanted to insert the ID in the first place …


                  The upshot here is to relate the tables via the primary/foreign key fields, not a name, and use e.g. one of the methods described to set the foreign key in TimeTable.


                  g4guitar wrote:

                  Now how do I get the display to show the customer's rather than the ID number?


                  With the relationship


                  Customer::id = Timetable::id_customer


                  and a value list using


                  1. field: Customer::id, 2. field: Customer::cFullName, “show values from second only”


                  create a record in TimeTable, click id_customer (formatted as a popup with that value list), select a name from menu (and actually insert the id); now this TimeTable record is related to the selected Customer, and any changes in that Customer record are reflected automatically in any related fields you display on the TimeTable layout.


                  A different approach: use a portal on the Customer layout to enter times, which will create related records and automatically set the key value correctly.

                  • 6. Re: Automatic record updating

                    Thank you everyone. Its all working now. I just had to change it to 'Popup' instead of 'Dropdown'. Once again thank you for your help. This community is brilliant.

                    • 7. Re: Automatic record updating

                      Now there is a new problem. When I do a search it does not recognise the popup fields. I can use Find but this is no good because I can only search one field at a time. I need to search all fields. Can anyone help here? Thank you.

                      • 8. Re: Automatic record updating

                        What is the difference between "search" and "find" as you interpret these words?

                        What fields are you talking about - have you got an up to date screen shot?

                        You can search as many fields as you like.

                        • 9. Re: Automatic record updating

                          If this is a new problem you should make it a new thread, especially as this one is marked as answered.


                          However, as Bruce points out, you can perform finds on multiple fields. You need to make each field search as a separate Find Request, that's all. For example, if you know that some records contain "blah blah" in field A and some records contain "blah blah" in field B, then you need to make one Request for "blah blah" in each field. That way, any record that contains "blah blah" in either field will be found. If you set a single Find Request to search for "blah blah" in field A and also field B, then that is what would be found—only records that contain "blah blah" in BOTH fields.

                          • 10. Re: Automatic record updating

                            Thank you Bruce and Keywords,


                            Firstly I am searching via the Search button in the top right on my Mac. Here I would expect it to locate all of records that contain 'Fred Wilson' yet it gives me 'No records match this find criteria.


                            Next when I try the FIND button I can't see anyway I can search all fields at once. I am also setting this up via FM Server/WebDirect so I need a way web users can do a find of all fields. In the example below I want to find all records in any field that contain 'Fred Wilson'.


                            Sorry Keywords I did post this question but no one answered so I though I would add it on here to see if I got an answer and low and behold. Am I doing something wrong on the other post hence why this one is attracting great answers and the other entitled 'Quickfind doesn’t seem to be working' is not getting a single reply in 3 days?


                            Thanks again for all your help. I know I must be missing something obvious here.

                            Search Q.png

                            • 11. Re: Automatic record updating

                              So what seems to be happening is the 'Search' field is only searching for the CustomerID and not their name due to the fact that the setup has to be linked to the ID to update automatically when I update the customer's record. So the question is how can I have it so it updates automatically when I make a change on the customer record and also have it so I can search all fields via the search in the Timetable. There also seems to be a distinction between the search box (top right on a Mac) and the Find option as the Find only allows me to search one field at a time. Sorry....Very confused. I hope someone knows the answer.

                              • 12. Re: Automatic record updating

                                Thank you keywords. The problem is I want to perform one search to look for "Blah" in every field without having to go through every field one at a time as that would be slow. The problem as I have discovered is I can search if the set up of the Popup list is only using the values from the Customer list but if I want it to use the CustomerID it doesn't work. I actually have to search via the CustomerID. This means having to look up the customerid first which is again is time consuming. So the question is how do I get the best of both worlds? How can I have the Customer names update automatically and have the search work for names across all fields? Below is how it looks for each option.11111.png

                                • 13. Re: Automatic record updating

                                  Now I am utterly confused about what you are trying to do. The image you have posted is of setup dialogs for creating Value Lists. What I understood you were struggling with was the process of Finding a record or set of records. I'm afraid I don't see any direct connection between the two. Can you be more specific about what you are trying to achieve?

                                  • 14. Re: Automatic record updating

                                    Thank you and apologies for any confusion.


                                    In short I have a timetable for classes which is one table. Each record represents a class and within each class are 7 available spots for students.


                                    The students are on a Customer table. The value list in the Timetable layout allows me to select students and place them into one of the 7 spots in any given class.


                                    Problem. The Value list setup on the left works fine for searching for a student but when I update their record on the Customer table (e.g. change their name spelling) this is not reflected on the Timetable layout. Now when I set up the value list on the right the opposite is true. The changes made on the Customer record update but I can no longer search all the fields because the search function is looking for the CustomerID not the name.

                                    Do you see my dilemma? I want both. I want to be able to search all fields and for the records to update on the Timetable form automatically.

                                    1 2 Previous Next