7 Replies Latest reply on Sep 8, 2011 4:56 PM by philmodjunk

    understanding how a relational field calculation work?

    AreliLopez

      Title

      understanding how a relational field calculation work?

      Post

      I have two databases where I have a field in each called "Part #" and "Part Number". The part numbers in the fields are in different orders and some of them are in one field but not in the other and vice-versa. I created a calculation field called comparison, where I used the "exact" function to see if they are equal or not. The result is either "match" or "not match". I obtain the correct result, but I would like to understand how does the related calculation work? How does it find the corresponding part number if its not in the same order as the other field and then compares it? Does it follow the inner join, left join, and right join operations perhaps?

        • 1. Re: understanding how a relational field calculation work?
          philmodjunk

          I think you are saying that you have two tables (Doesn't matter if they are in the same file or not) with this relationship:

          TableA::Part # = TableB::Part Number

          If your layout is based in TableA, then this relationship will match to the all records in TableB where Part # = Part Number. The match is not case sensitive so a part number of a1001 will match to A1001. If you use a portal to Table B on this layout, you will see any and all records with the same part number listed in the portal. If you place a field from TableB on this layout you will see data in that field from the "first" related record in table B.

          What record is first depends on two possible factors. Either you've speciified a sort order for the relationship, in which case the related record that sorts to the first position according to that sort order is "first". If it is not sorted, the related record that was created first is "first".

          If you use this relationship on a layout based on TableB, the results are flipped. Now you match to records in TableA that are related to the current record in TableB.

          • 2. Re: understanding how a relational field calculation work?
            AreliLopez

            Ok I understand...

            I created another calculation field to check if the quantities for each part number match or not. I have a quantity field in both tables. If the quantities match, I want to output "match" otherwise, "not match".I assumed because I already created the "=" relationship, I don't have to create another relationship in order to check if they are equal or not. But it doesn't seem to work. If the part numbers match and the quantity does not match, I obtain the right result( a "match" for the part number, and "not match" for the quantity), but if the part number does not match and the quantity does match, it doesn't work(a "not match" for the part number and the quantity)..what other kind of relationship can I create in order for the comparing to work correctly?

            the calculation for my calculation field for the quantity looks like this:

            If(quantity1=quantity2; "match"; "not match")

            • 3. Re: understanding how a relational field calculation work?
              philmodjunk

              I'm afraid I can't quite figure out what you are trying to do here and that is keeping me from making any useful suggestion here.

              What are you trying to do? Why do you want to match part numbers in the first place between these two tables?

              Why are you comparing quantity fields to report a "match" or "no match"? (That does not make any sense to me here.)

              • 4. Re: understanding how a relational field calculation work?
                Sorbsbuster

                I'm guessing it's something like a Stock File and a Stock Count Result file, where you are trying to compare if the quantity for a Part Number in one file is the same as the quantity for the Part Number in the other file?

                • 5. Re: understanding how a relational field calculation work?
                  AreliLopez

                  sorry for the confusion.

                  Well I am trying to get familiar with filemaker. So I am using filemaker to compare two separate files that suppose to contain the same information. They are excel files that contain a list of materials(part numbers) with corresponding data such as the number of quantity, measurements, manufacturer, etc. I import them into two separate filemaker files. I am only interested in knowing if there are part numbers that are equal to the other part numbers in the other file as well as the corresponding quantity. I want to report back if each part number and corresponding quantity are equal or not. Thats why I am a match relationship for the part numbers and using a calculation field to check if part number and quantity field match or not. 

                  P.S. The part numbers in both files are not listed in the same order and there are errors such as, not equal quantities when part numbers are equal, not equal quantities and not equal part numbers, or equal quantities but not equal part numbers.

                  • 6. Re: understanding how a relational field calculation work?
                    AreliLopez

                    Sorbsbuster,

                    Yes, I guess you can think of it as a stock file. I am comparing in both files because I want to check for all existing part numbers not just from the current table records. Or is there another way to show all records from both tables into one layout? I have only been able to show the matching records from the current table chosen in the "specify calculation" dialog box.

                    • 7. Re: understanding how a relational field calculation work?
                      philmodjunk

                      The order of the records makes no difference. In a relationship, the first record located anywhere in the related table that has the correct matching values will be listed as a "match".

                      If you use this relationship:

                      Table1::partNumber = Table2::partNumber AND
                      Table1::Qty = Table2::Qty

                      You can place a portal to Table2 on your Table 1 layout and see a list of all matching records, if any, located in table2. I suggest a portal as more than one record in table 2 could matchthe same record in table 1.

                      If you place a portal to table1 on a layout for table2, you'll see the same type of matchup but from the perspective of each record in table2.

                      Once you can see which records have a matching record in the other table, then what do you want to do with this info?