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.
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".
"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.
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"?
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.
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.
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.
Kit description: Little Red Wagon
If that's what you want, I would add another table and another table occurrence of Inventory like this:
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.