7 Replies Latest reply on Oct 4, 2013 9:04 AM by philmodjunk

    Crosschecking External File

    icelille999

      Title

      Crosschecking External File

      Post

           Hello

           I'm quite new to Filemaker and scripting. I'm having an issue with having the ability to create something for me which would cross check 1-2 specific fields in a certain records that have different certain codes in an external file. I attempted to create some sort of solution by creating a calculation field, with the following inside: 

           In Kit =

            

           If ( Inventory::Manufacturer Part Number = 2::Vendor Part Number; 2::Assembly Number ; "false")

            

           With that, the only solution I ever get is false.. but I double checked the names and how to format an if command, and it's still wrong. I even tried importing the records into a different table in the same file, but still.. nothing. If someone could please point me to the right direction somehow and help me figure this out, I would be very grateful. 

        • 1. Re: Crosschecking External File
          philmodjunk

               Inventory::Manufacturer Part Number

               refers to the field named "Manufacturer Part Number" via the Tutorial: What are Table Occurrences? in Manage | Database | Relationships named "Inventory".

               2::Vendor Part Number

               refers to the field named "vendor Part Number" via a Table Occurrence named "2"--a very nondescriptive name for a table occurrence.

               Is there a relationship in Manage | Database | Relationship that links "Inventory" to "2"? What are the match fields used in that relationship? What is the operator used?

               How are you using this calculation? In a script step? In a field of type Calculation? In an auto-entered calculation?

               In what context does this calculation evaluate? From Inventory, 2, or some other table occurrence in your database?

               The relationship, if any, that links these two table occurrences and the context in which the If function evaluates will determine what value from Inventory is being compared to a value in "2". That in turn controls what value is returned by your If function.

          • 2. Re: Crosschecking External File
            icelille999

                 Yes. There are defined relationships between "Inventory" and "2". "Inventory"'s "In Kits" and "Manufacturer Part Number" have been linked to "2"'s "Vendor Part Number". Also, sorry,  what do you mean by what is the operator used? 

                 I am using this calculation in a field of type Calculation. It comes from the Inventory table, in the field "In Kits". 

            • 3. Re: Crosschecking External File
              philmodjunk
                   

                        "Inventory"'s "In Kits" and "Manufacturer Part Number" have been linked to "2"'s "Vendor Part Number"

                   Are you saying that two different fields in Inventory have been linked to Vendor Part Number in 2? I suggest uploading a screen shot of Manage | Database | Relationships to show me this. That does not sound like a workable relationship.

                   The default operator is =. Unless you have double clicked a relationship line and changed operators, that will be the operator used and you'll be able to see this in Manage | Database | Relationships except in some cases where more than one pair of match fields have been selected.

                   

                        It comes from the Inventory table, in the field "In Kits".

                   That tells me that, most likely, the "context" for this calculation is Inventory, but it's possible to specify a different table occurrence context via the drop down at the top of Specify Calculation and that would change how this calculation evaluates, so let me know if the context is NOT Inventory.

                   I suspect that the extra match field, "in kits" is keeping any record in 2 from matching to any record in Inventory. That would be consistent with the results that you are reporting. In that case you are comparing the value of Inventory::Manufacturer Part Number to an empty field and thus the values are not equal.

                   I would guess that if you set up a relationship that only matched Inventory::Manufacturer Part Number to only 2::Vendor Part Number, that you won't really need this If function.

              • 4. Re: Crosschecking External File
                icelille999

                     Oh yes!! It does work now! Just changing the relationship so that the Inventory:: Manufacturer Part Number to only 2::Vendor Part Number worked wonderfully. Thank you so much. Also, just to check, would this allow for multiple finds to come up in "In Kits"?

                • 5. Re: Crosschecking External File
                  philmodjunk
                       

                            Also, just to check, would this allow for multiple finds to come up in "In Kits"?

                       I have no idea as I have nearly no information about your database on which to base an answer.

                  • 6. Re: Crosschecking External File
                    icelille999

                         Well for a few records, it only lists one of the "Vendor Part Numbers" in "In Kits", when there should be multiple "Assembly Numbers". In the table that "Vendor Part Numbers" Is coming from, there are some "Vendor Part Numbers" that are repeated in a long list of records. Typically, when the "Vendor Part Numbers" are repeated, they give different "Assembly Numbers", but when coming back through "In Kits", there is not a list, only one single Assembly Number.

                    • 7. Re: Crosschecking External File
                      philmodjunk

                           See if this is what you mean:

                           Some of your inventory items are "kits" assembled from multiple other items in your inventory. When you refer to a "kit" item, you need the list of all the parts and their quantities that make up that kit.

                           Example.

                           Kit description: Little Red Wagon
                           Parts:           Qty
                           Wheels        4
                           Axles            2
                           WagonBed   1
                           Handle         1

                           If that's what you want, I would add another table and another table occurrence of Inventory like this:

                           Inventory----<KitList>-----Inventory|KitItems

                           Inventory::__pkInventoryID = KitList::_fkInventoryID
                           Inventory|KitItems::__pkInventoryID = KitList::_fkKitItemID

                           Inventory and Inventory|KitItems would be Tutorial: What are Table Occurrences? with the same data source table.

                           For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                           This not only allows for kits like the red wagon example, it even supports creating Kits that are made up of other kits.