6 Replies Latest reply on May 8, 2014 12:41 AM by FrancoT

    Correlation between 2 separated identical master tables and a 3rd shared  one

    FrancoT

      Title

      Correlation between 2 separated identical master tables and a 3rd shared  one

      Post

       We have 2 identical master tables, same type of fields and names etc, each containing records coming from different sources. For security and management reasons such tables (and records)have to remain separate. However there would be sometimes the necessity to sort out with a common key, records from both the tables and find them listed in common fields into a 3rd shared table(or portal or any other different solution). The perfect goal would be also to be able to update such sorted records and find they modified in their original master tables (as they would be correlated).

        • 1. Re: Correlation between 2 separated identical master tables and a 3rd shared  one
          philmodjunk
               

                     We have 2 identical master tables, same type of fields and names etc, each containing records coming from different sources. For security and management reasons such tables (and records)have to remain separate.

               Please explain why that is necessary as this design choice significantly complicates the design and function of your database. There are ways to use Manage | Security to limit different users as to what records in the same table may be viewed and/or edited if that is the concern here.

               What you want to do is possible, but it won't be a simple thing to set up to the point where you are sure to always have correct up to date data in both tables.

          • 2. Re: Correlation between 2 separated identical master tables and a 3rd shared  one
            FentonJones

                 Since sometimes a business may do this "separate" databases (for their own reasons, despite what we may say :-; and sense I already wrote it:

                  

                 OK, I'll guess how I think I'd do that. First, each table's records must have a unique serial ID, which does NOT match each other. In other words, if you just used numbers, such as: 1, 2, 3, etc., than they would match; you'd need to add text, such are: A1, A2, A3 (for one table), and B1, B2, B3 for the other table. This could be done easily with just a calculation field (text, indexable); "A" & existing number serial field, then "B" for the other table; so each now has unique serial IDs.

                  

                 You could then Import these IDs, records, of each table into a 3rd table/file; which would now have the record count of both together. Create a relationship from 3rd to each of the other 2 (parent) tables. Each record will match either one or the other.

                  

                 Create each field in the 3rd. (I'd just copy/paste them.) Then, change each of them to a calculation. [Copy the field's name first, so you can paste it into calc. I'd also have created a calc. field in each file, z_c1, = 1, so I have a quick and easy method to test (also useful for counts).]

                 Case ( 

                 not IsEmpty (A relationship::z_c1); A relationship::field name );

                 not IsEmpty (B relationship::z_c1; B relationship::field name

                 )

                 Other than the field name, the calc is the same, so can be copy/pasted for each field. Also change result as needed, for number, text, etc.. [ If you let the field names remain the same (at least at first), FileMaker will likely support copy/pasting layouts from either of the 2 existing table/files; IF you create the relationship name needed for that layout. This will not support related fields on the layout however (much anyway).]

                  

                 When you've done all that, the 3rd table/file can now show all the data of the other table/files, with each in its own record. Almost all of it is unstored, except the unique ID (which keeps the size of the file/table down).

                  

                 From then on you just need to add whatever new records from the other 2 table/files by adding them via their unique ID.

                  

                 There would be no need to worry about modifications to the record data, as the 3rd table/file is showing the current data in the other 2 table/files; only the ID is actual table field data (and it is the same as one of the other 2). 

                  

                 Yes, the 3rd table would not be real fast on sorts, etc.. You might consider adding some other fields to the Import routine. The "timestamp created" would be fine (as it never changes); and possible any other which NEVER changes (which tend to be few).

                  

                 Obviously, you'd also need to add any new fields (as calc) if you add a field to the other 2 table/files. 

            • 3. Re: Correlation between 2 separated identical master tables and a 3rd shared  one
              FrancoT

                   Phil is right to ask why keep life complicate when it would be possible to simplify it. However as said there are various reasons - mainly the data belong to different Projects - now we are trying with 2 master tables, later it will be possible to extend to more - that need to remain normally separate.

                   Back on the subject some questions to Jones:

                   a) it is clear the instruction to create unique IDrecord for each record of the 2 master tables and to import/copy all such data on the 3rd table, in order to have a "double way correspondance". I also guess you intend that the IDrecord fields need to be the "link" for the relationship between table 1 and 3 and table 2 and 3 respectively. Do you confirm?

                   b) it is not very clear the suggestion to define the calc. fields in each of the 3 tables Z_c1=1. Is this just to have a tool for an easy test or else? 

                   c) on the understanding that all the above will eventually work as explained and expected, again it is not clear if and how the 3rd table calc. fields data (all of them with the exception of the IDrecord will be of calc. type! ) can be also modified operating from such last 3rd table.

                   thankyou in advance

                   F

                    

                    

                    

              • 4. Re: Correlation between 2 separated identical master tables and a 3rd shared  one
                philmodjunk
                     

                          mainly the data belong to different Projects

                     That is not a reason to have two master tables. Data from more than one project can be managed in the same table. And if you see the need for your method to add even more "master tables" in the future, this approach becomes even more unwieldy.

                • 5. Re: Correlation between 2 separated identical master tables and a 3rd shared  one
                  FentonJones

                        

                       I would agree with Phil, that using different databases is usually a sign that a single database would be better. It is hard for us to really know exactly why you're going with separate databases. The fact that you want to also seem them as the "same" sometimes makes us even more suspicious. What I'm offering for that is a 3rd database; but which has very few "regular" fields, an ID which can match one of the other two, and the rest of the fields being merely calculations to view the data from the others. If you were saying "I want to copy all the data from each of the others", then I would not really support that. We're just looking for a "view" of them.
                        
                       The 3rd database will have very few fields which are actually "data" fields; only the ID, and possibly "date created from the other two databases", etc., so you can sort by date created, etc.. Those fields would be ones which never change (else any modification in the other tables would make them out of date).
                        
                       Almost all of the other fields in the 3rd database would be just calculation fields, looking at its "parent". Each record in the 3rd will only match one or the other of the two.
                        
                       a) Yes. Each of the 2 existing databases need unique IDs which have NO possible matches to the other. Then, when you Import all of the records from both into the 3rd, into the SAME ID field, it would still have unique IDs.
                        
                       b) 
                       Case ( 
                       not IsEmpty (A relationship::z_c1); A relationship::field name );
                       not IsEmpty (B relationship::z_c1; B relationship::field name
                       )
                        
                       z_c1 is just a calculation field in the 2 dbs. The result is just: 1
                       I use it as a trick. It is the fast, simplest way to find out "not IsEmpty"?
                       Also, that part of the calculation would be the SAME for EVER calculation field in the 3rd database. You will need to change the right of the calc., to say which field, but, because of the z_c1 you will NOT need to change it. Hence you'll just have 1 change instead of 2; which matters if there's many fields. The z_c1 calculation is very useful; I use it on most of my more complex files (easy to count also).
                        
                       c) I'm not sure exactly what you mean; but it seems you're asking, "Can you modify the data in the "1st" and "2nd" db table records from the 3rd db table record. Yes, I suppose you could. But, in that case you'd be doing just about the "opposite" of the above. 
                        
                       Each record in the 3rd db table has a match to one of the 1st OR 2nd. You would need to put the field using the relationship from the 1st on the layout, then put the field from the 2nd on the layout also; the real fields. You could then enter/modify data into the one that is connected (only 1 will be). You would likely do this in two portals, on a Form view layout, one to each of the two relationships. ( You could also show the local calc. fields of 3rd db; but it would just show whatever the modified field was; it is after all, the same data; I'd likely do that, on a DEV layout, just to test)
                        
                       Basically:
                       b) Is what you use to "view" the data from the other two databases, as IF it was real data, in List views, other layouts. A field which is in two other dbs would appear to be in only 1 (as an unstored calculation file, which can show either).
                       c) Would be if you wanted to see the "real" data, in order to modify it. That is he opposite of b). You'd need to put EACH of the field from the two other dbs. Only one would be enterable on each record, as only 1 of the relationships would be connected.
                        
                       So, you don't really "need" b); you could just put the darn real fields BOTH on the layout in the 3rd database. But will would NOT make a pretty view, hard to view. 
                        
                       I'd recommend you built 3 small files and test some of this. It is not a big deal; but you'd want to see how it's done.
                        
                       P.S. The only time I remember doing this "extra table" method was when someone needed data from a table, but wanted so many rather messy changes, naming changes of the fields (yuck), etc., for some "web designer" stuff. I didn't want to mess up my real table with this junk. I know however that it would work with 3 tables also; I've sometimes had calculation fields which looked at more than one table, and had to deside which; similar methods.
                  • 6. Re: Correlation between 2 separated identical master tables and a 3rd shared  one
                    FrancoT

                         All your comments, suggestions and instructions are now well understood, thankyou very much!

                         While for future development is defenetely logic and useful to adopt the policy to gather separate but similar databases in a single one, it is currently necessary to  arrange a solution as such suggested 3rd file as global template of the 2 parent files.