I have a database for automobiles. Each automobile is identified in the system by make/model and VIN. There is a related table that contains technical documentation related to the make/model of the automobile. When you find the automobile, a portal generates a list of these technical documents (Layout#1).
Some of the documents are VIN number specific. (Layout#2) There is a repeating field (VIN) that lists each VIN in separate repetition that is the specific technical document is related to (sometimes there is 6000+ VIN per document). I have a global field on Layout#2 called "globalVIN". I have a script that enters in the VIN for the automobile that the client is looking at.
My hopes are that I can have a field that calculates Yes/No on Layout#1 inside the portal next to the technical document that will tell the client whether or not the technical documentation is applicable to their specific VIN.
My issue is that every calculation that I come up with on evalutes the first repetition in the repeating field or the first value in the non-repeating field and will not review the remainder of the VIN to see if they match. My calculation kind of looks like this:
If ( Extend (VIN) = globalVIN ; "Yes" ; "No")
I have attempted many different versions of this calculation. Is there anyway to get a calculation that evaluates whether or not a value is in one of the repetitions in a repeating field or is a value separated by a carriage return in a non-repeating field? I want to avoid creating another table that lists each single document as one record for every VIN. Any guidance you can give me would be greatly appreciated.