"calculate value" only works once!!
Here is my problem:
My database purpose is to display box contents. Let’s say I have a box with 10x10 wells. I created a file (let’s call it “Chocolate database”) where I introduce all the information regarding each item present including the “Box Name” , “location of the box” and the “position #” (from 1 to 100) for each item (eg: the item “kit kat” is in box “candy 4” in the “position 1” and “Mars” is in box “candy 4” and “position 2”).
All the positions are taken in the database, even if in reality there is no chocolate at that well on the database is written “empty well” so it counts as a record as well.Then I created another separate file where I made a layout with the “layout/grid/top view” of the box and created a field for every square, so I have A1,A2,A3,….. and also the box “name” and “location” (let’s call it “Individual Boxes”). I established a relationship between this database and the one with information for the individual items, via the box name. The purpose was to introduce, by choosing in the displayed list, the box name and it would automatically fill all the remaining fields, eg: if you choose box “candy 4” it would automatically fill A1 field with “Kit Kat”, and field A2 with “Mars”, and so on. To do this I configurated each of the A1,A2,A3,…. fields to “calculate value” and introduced the following in the A1 field:
If ( Chocolate database::Position # = 1 ; Chocolate database::Cell line name ; "error" )
If I do this it works, but then I go to the A2 field, do exactly the same thing but chance the position number from 1 to 2 and I gives the “error” message!!
If ( Chocolate database::Position # = 2 ; Chocolate database::Cell line name ; "error" )
I tried doing it for field B10 and the correspondence position 11 but it also gives the “error” message!!
Does anyone have an idea why this happens? To me it makes absolutely no sense!!
Hope you can help.
Thank you for your time.