text concatenation not working in related fields AND storing calculated result
Hello folks, I'm having trouble with text concatenation.
I have Patch and Component tables in a one to many relationship through the PatchID field (number). The Patch table is related to the Vineyard table through the Vineyard ID (text) and looks up the Variety through the variety ID (text).
The component table contains fields Vintage (number), batch number (number) and type (text)
I am trying to concatenate text on the Component form through the Component:Component Code field - as an unstored calculation, calculated as text.
Problem 1: ideally I would like to store the result of this to use in a dynamic dropdown which only shows current components (thinking to self - how to tell when component is no longer current?)
Component code=Ptach:VineyardID & Component:Vintage & Component:type & Variety:VarietyID & Compoentn:Batchnumber & Patch:Farming method
I select the patch on the component layout with a drop-down field from Patch:Patch code using a manually assigned patch code (the patchID is just an autonum for indexing). The output should look something like SEM13RSHZ1O (Semmler, 2013, red, Shiraz, batch 1, organic).
Problem 2: Instead, my output is 2013R1 ie it is only picking up fields from the component layout, regardless of whether they are text or numbers.
I originally thought it may have been my relationships which go like this
1 Vineyard -> many patchs
1 patch ->many components
select a component through a dropdown on calculated field Component:Component Code to use the component code to build
1 blend -> many components
I have tried:
1. altering the relationships to component:ComponentID and Patch: componentID (which violates unique values in component:patch)
2. making this a dual criteria join (match Patch ID and component ID) (which violates unique values in component:patch)
3. using getastext within the calculation for any number (no difference to outcome)
4. placing lookup fields in the component table for vineyard, variety and farming method (text does not auto-enter from the related tables).
This suggests my relationships are wrong. Can you please advise whether I require:
a. a self-join, and if so which table/s
b. an indexing table eg patchID and componentID fields only, sitting between patch and component fields
I'm sorry this is long-winded, however I have spent hours trying to resolve this one.
Any help you can give would be greatly appreciated. Thank you in advance.