3 Replies Latest reply on Nov 15, 2010 7:04 AM by filemakernewbie

    synchronizing and  updating data using two tables with auto-complete option



      synchronizing and  updating data using two tables with auto-complete option


      Hi, as the name suggest...I am a newbie

      I am currently using FM 11Pro (trial) on a Mac with Snow Leopard as a single user

      I am currently working with two tables (Ppl Info) and (Doc Info)

      In Ppl Info table (table 1), I have following fields: Last Name, First Name, Address, City, Postal Code, Phone Number, Ref Doc's Last Name, Ref Doc's First Name, Ref Doc's Address, Ref Doc's City, Ref Doc's Postal Code, Ref Doc's ID No

      In Doc Info table (table2), the following fields: Doc's Last Name, Doc's First Name, Doc's Address, Doc's City, Doc's Postal Code, Doc's ID No

      Thus far, I have set up a relationship between two tables so that when ID no's match, it will automatically display all corresponding info with same ID no. from table 2 onto table 1 layout.  (thank you PhilModJunk) Problem is when there is no matching ID no,  I can't update ref doc's data in table 1 in browse mode.  Ideally what I would like to have is that, as ref doc's last name is inputted in table 1, FM will automatically look up same last name from table 2, and auto-complete all the corresponding info (address etc) in the designated fields of  table 1. If there are more than one doc with same last name, FM will then search for all the first names with same last name, gives the user an option to pick the first names and then automatically fill in the corresponding data. If there is no matching last and/or first name, then FM allows user to create a new data for ref doc in table 1, which will synchronize and update table 2.

      After reading this post, I do feel like this is too much to ask for FM to do.  Is this even possible?

      Thanks in advance

        • 1. Re: synchronizing and  updating data using two tables with auto-complete option

          No replies so far...

          hopefully someone can help me out on monday...? :)

          • 2. Re: synchronizing and  updating data using two tables with auto-complete option

            Yes, it is possible. In fact, this is a problem which all developers have to solve in some way in almost every database we build. It is just a little difficult to say exactly what the method should be. There are two tasks involved, and some decisions about what "reduntant" data should be "copied" to the other table. 

            The general rule is, "only populate (and maintain) redundant data when necessay (or convenient, or when told you have to by your client, and there is no reason why that would be actually "a bad idea" :-).". In FileMaker 10 script triggers make this maintenance much easier; but I still resist overdoing redundancy.

            The reason I'm going on about the above is that it is one of the reasons you're not getting answers (well, mostly because it's Sunday). It sounds like you want to "copy" too much redundant data. Address especially should not be copied, unless you've got a database that does a lot of operations on addresses. 

            Basically, if you have a relationship you can see and edit data in a related table, especially thru such a solid connect as two indexed keys in adjacent table occurrences. The speed will still be good. You do not need to "copy" it to use it.

            Names on the other hand, is always a question. If you are going to Find or Sort (reports), etc, it can be convenient to have the First & Last names in another table. But then you must maintain them, pass changes from the main one (the name entity's table) to the other; a script trigger can do this.

            So, the other part of the problem is how to create that first record. It is common to be in a child table and which to be able to quickly create a parent record. And then there's duplicates. Duplicates are the bane of data entry. Since everything depends on the Person ID (whatever you call it), duplicates are a bad thing (annoying at best). Testing for duplicates in names is critical to almost any database, especially as the database grows. 

            I think a filtered portal, or dedicated Find layout is the best method. In other words, filter a portal by a global field. So that typing a few letters narrows the results. It only takes about 5 letters to filter 100,000 names down to 10 or so; which will show fine in a portal. I would use a wide portal, or multiple portals, to show a fair amount of other info about the found people, address, email, phones perhaps. In case there's 2 people with the same, or near the same name, you can tell them apart. 

            This filtered portal can be a dedicated layout. Or it can be hidden within a largish tab object on the main layout. That solves one problem which occurs otherwise; knowing which child record you're on.

            Personally I like to pop up a little "windoid" (made modal), sized and placed nicely over to the side of the data entry layout, or beside it if the screen large enough. But then you need to learn how to create an control a window. It's not that hard, and we've (all) got examples.

            The window would have a Tab Object, with 2 tabs, one for the filtered portal. The other for data entry fields from the parent table (People in this case?), which could be tied to the new ID created (you go create the new People record, grap its ID, return and set it into a global ID field, to target the People fields). If you do this, then you are completely portable. You could put this mechanism on any layout.

            This layout could be attached to a "person" table occurrence, or to a child table's layout. The only thing needed is a global field. It then points to another occurrence of the People table, to filter the people names.

            The operation is: Filter and look at the people. If you see the one you're looking for, you click it, a script runs to set its ID wherever, and do whatever else. Or you don't find them, so you click a New Person button. Which creates a new person, and flips you to a dedicated date entry area, with people fields (minimal).

            If a new window, the layout is dedicated to this, and made modal, so that you can click a button to set the ID to where you want it to go; and it knows where that is. It is modal so that you cannot easily wander off, since it is dangerous to leave such a window open, because of the buttons setting the ID to somewhere; that somewhere cannot change. I capture the ID of the child I've come from, and will block setting the ID if you are no longer still on that child. Makes it safer.

            The above is mostly just information, not detailed instructions. But I find that it's better to learn the logic before/while/after you're trying to build things. There's more than one way to do this, but the logic and problems are much the same.

            • 3. Re: synchronizing and  updating data using two tables with auto-complete option

              Thank you for your reply. It has a lot of info on it, so I think I will try it in step-wise manner once I am at home.

              First step....learn to set up unique ids for record in each table. I will let you know how it goes. :)