I have two databases served on FileMaker Server Advanced 22.214.171.124, Catalog and Parts.
A two-field relationship exists between the two databases:
In Catalog is a calc field, VendorItem. If a related record exists in Parts, it concatenates the VendorID, a hyphen, and the ItemID.
In Catalog is another calc field, ItemToOrder. Its calculation is a Case statement including this criteria/result pair:
This is set up this way because the Catalog is actually related to two different external data sources, one being OE parts; the other being Aftermarket parts.
The purpose of the Item To Order field is to show just the part number from the OE parts table if one exists, and failing that, show a concatenation of the vendor and part number from the aftermarket table if one exists.
All the associated fields are text fields. All the calculations are set to return results as text.
Here's the problem:
It all works as expected so long as the ItemID in Catalog and the corresponding ItemID in Parts contains a numeral. If the ItemID is only alpha characters, the VendorItem field in Catalog (the one that concatenates the VendorID and ItemID) does display correctly (so the related record is being found), but the ItemToOrder field remains blank. Only if I key a numeral anywhere in the values of the ItemID fields of Catalog and Parts does it populate. I've checked and re-checked everything I can think of to make sure nothing is looking for a numerical value. Everything else about the Case calculation also works correctly. If there is a matching ItemID in the OE parts table, that one displays in ItemToOrder instead of any matching ItemID that may or may not exist in the aftermarket Parts table. The problem affects only a very small handful of aftermarket ItemIDs which consist entirely of text characters and no numerals. The presence or absence of hyphens in the ItemIDs also does not cause any problem. It's only when the ItemID contains only alpha characters (with or without hyphens) and no numerals that the problem occurs.