In a previous post, I asked questions to better understand the auto-enter DATA behaviour. I have also asked about Relook-up script step before but today, I would like to understand LOOKED-UP better, by watching it in action.
I am reviewing all the calculations in the solution I created last year, to determine how I will integrate the new contingencies and functionalities my client requested.
As I review, I stumble on things that I obviously did without knowing what I was doing (which means some things work by luck...), I try to understand what is really happening, what does FileMaker do with the instructions I gave it. I feel it is important for me to refine my understanding so that there are less guesswork and more sound strategies in my development.
Since I have come across a bunch of fields that do not seem properly set and one which isn't behaving completely well, I have made some images to support my following questions
Image A: The table Product
1) As a rule, for check-box indicators, I call the field "isWhatever" and the value list contains only the value 1. For some reason, I set this as a text field (my other boolean type fields are set as number field). It should be a number field right?
2)isRecurring is a field that must be set manually by the user so there isn't any autoentry; however, I have set a validation because it is very unlikely, although possible, that a product meets both conditions of isBank and isRecurring; to alert the user if he checked both. The user can click ok and then correct or not his selection. Technically, the validation failed, hence the message. I'm glad FileMaker does not remove the check mark but I do not understand why, given that the validation failed?
Image B: The table EstimateLines
1) In the EstimateLines::isRecurring looks up Products::isRecurring. The field Products::isRecurring (imageA) is set to adopt a boolean behaviour, its value can only be 1 or nothing. The box "Don't copy contents if empty" (1) is checked. Does that do or don't do anything here? Solved: if there is a bolean field looking up that value, if the value empty is not copied, there will be an issue.
2) isRecurring_r was created to convert the 1 to the string r to indicate recurrence on the estimate line (see imageC). I set it to the type number (I guess I duplicated the isRecurring and forgot to change the type). What I do not understand here is that despite my error, FileMaker does return "r", which is a string, not a number? If a string is in a number field, isn't it ignored in calculations?
3) It seems that I used to believe that taxes are not numbers because again here (4), I have set the field as text while its value can only be 1 or nothing. Nevertheless, in calculations, it would effectively compute the number value of the 1; would it take the value "" as if it was 0 in calculations?
4) Here is something peculiar I did: I have given 2 auto-enter methods (5), (6), what does FileMaker do with these instructions? Does it use the Data value or the looked up value?
Image C: The Estimate Layout's Portal used to build the quote's list of products.
1) In this portal, which input method I did not touch from the starter solution, clicking on the name of the item (which is a match field with the field item in the Product table) produces a dropdown list (the value list is based on the field item in the Product table).
Note: in the rebuild I am doing of the solution, I changed the selection method; but for now I need to make some quick changes
What works: when selecting the item, the estimate line looks up for the price, deposit rate, isTaxable and is Recurring. The user enter a quantity, an adjustment rate if he wants. If the user change the Qty, adjustment, or the taxable indicators, the line updates and the price is adjusted in consequence. If the user click again on the name of the item and changes it, again, the line updates EXCEPT for the recurrence indicator.
What doesn't work. If the user change a product that did not have recurrence for a service that has, the line will update correctly showing the recurrence indicator BUT the reverse isn't true, here (3), the user changed his product selection which was initially a recurring service for a piece of hardware. However, (4) the recurrence indicator is still there. The user can manually remove it, but will never think of checking this. I tried changing the isRecurring_r to a calculation field but it does no good since it takes isRecurring which does the lookup.
Solved: I unchecked "Don't copy content if empty" and now it works like a charm. When I looked back at isRecurring definition, I realized that if isRecurring is empty (which is the case for hardware products), if it does not get copied, then the indicator will not be overriden in the estimate line.
I now understand better how the relook-up work. When you define a field to look up a value, it does it initiially, when the record is created, and whenever a field in the same table which refers to it is updated. If the field already exist, and for example, I change Qty on my estimate line, the price, which is set to look up its value will not update because Qty doesn't refer to it.
To force a relook-up, I have two options: making a change to the field which is the match field of the relation between the table of the fields that are looking up and the table of those being looked at. For instance, here, when the user changes the name of item he selected, the entire line updates. This is because the fields item/name of item are the match fields that link product and estimate line .
The other option is to use the script step relookup. When using that script step, it is tempting to type in the name of the field we want to update. WRONG. The target field that must specified is the match field between the looking up and looked at tables.
On consideration, or caveat, is that you cannot only change one of the fields that look up, any change to the match field, whether user selection or scripted, will force the update of all the table's field. If the intention is to preserve historical data (eg. in old Estimates, you may want to still see the price at that time, although the price has now change; if for any reason, on old documents you may need to do some updates on certain fields only (eg deceased people records), it is better to consider using a variable and a set field.