Looping through fields (same record) in FM 11

[messadua]

Hello everbody,

 

Here it is my problem: I would like to loop through several fields of the same record, in the same multitabbed layout, in order to run a validation check (like if...else ) and in case replace the field content. Specifically, if the script finds a field with the content of 0.00 or "?" (ineffective computations) would like to empty the specific field, cleaning it up.

 

I have read here an interesting topic on creation of dynamic arrays (in my case an array of fields names and contents) using the "execute SQL" function available in FM 12 and 2 variables ($FieldContent and $Counter). Unfortunately my environment is and will stay for a while, in FM 11.

 

I know also that a dummy way of doing this is going to the first field, than "go to next field" in tab order, run the validation check (and possibly the replacement command) in a loop. The loop will exit when $ActiveField = $FirstField.

Unfortunately, this way is impractical since my fields are all organized in a Tabbed layout so that going to first field to the next is not done in the loop (I would be forced to copy all fields in a new, temporary, layout)

 

Is there any elegant, and not too difficult way to accomplish this. In other words, how you can loop through an array of fields in FM other than Go to First field and then Do the next field commands ?

 

I could also hard code the operation field by field, but that would be really inelegant

 

Thanks a lot in advance !

 

Diego

[eos]

What's wrong with validation on entry? Anyway …

 

… you can loop just fine through your fields in FM 11:

 

• build an array (actually just a value list) of field names using FieldNames (); be aware that the function doesn't give you (necessarily) all fields in a table, but those on the specified layout; so maybe create and use a purpose-built layout containing only the desired fields.

 

• loop through your field name array, and in each iteration use GetField () and Set Field by Name [] to dynamically get the contents of the respective field and set it (or not), according to your calculation(s).

 

Is there any elegant, and not too difficult way. 

 

Maybe not the best project to start after you just opened your new FM software for the first time, but it's not rocket surgery! If you need details to flesh out this sketch, let us know.

 

btw, if I was Lee, I'd ask you to update your profile … Don't get caught!

[bcooney]

"0.00 or "?" (ineffective computations) would like to empty the specific field, cleaning it up."

 

But, these are calc fields? How are they set? How did they get to be invalid in the first place?

 

Seems to me that you haven't adopted a "FileMaker-way" mindset. Perhaps if you expand on your scenario and workflow, we can provide a better technique to accomplish your goal.

 

-Barbara

[comment]

Instead of using FieldNames(), you could select the relevant fields explicitly by setting a variable to =

List (
GetFieldName ( MyTable::FirstField ) ;
GetFieldName ( MyTable::SecondField ) ;
...
GetFieldName ( MyTable::LastField )
)

then loop over the values of the variable as suggested by eos. This way you will work entirely at the data level, with no regard to what is or isn't on the layout. See here for something quite similar:
http://fmforums.com/forum/topic/65087-search-multiple-fields/


That said, your question raises an eyebrow because:

1. As Barbara noted, the result of a calculation - whether set by script or within a calculation field - can be controlled by the script/calculation itself, without requiring another script to "clean up".

2. The need to perform the same action on several fields suggests that your data is not normalized; it sounds like you are using multiple fields where you should have multiple records in a related table instead. This is also the main reason why the solutions do not seem "elegant": the elegant solution would start by finding the problematic records.

 

 

I know also that a dummy way of doing this is going to the first field, than "go to next field" in tab order, run the validation check (and possibly the replacement command) in a loop. The loop will exit when $ActiveField = $FirstField.

Unfortunately, this way is impractical since my fields are all organized in a Tabbed layout

 

I don't see why that would be a problem - the only thing that matters is whether the fields are included in the layout's tab order.

[Lee Smith]

 

btw, if I was Lee, I'd ask you to update your profile … Don't get caught!

LOL

 

@ messadua

I see you updated your FileMaker information, please also let us know what Operation System, and Platform (cross platform) you are developing on. Here is a link for this. My Profile

 

The more we know, the better we can help.

 

Lee

[messadua]

Ok Guys (and gals !)

Here it is the real setting of my solution: I have a layout with many measures (i.e. fields) as the result of a diagnostic test, some of them are supposed to be inserted by the user, others are derived by computations. The computations are performed through a script working in the following way

 

Set field C = A + B / D

Set field E = (C + F) x 2 

 

etc...

 

Of course, when the user does not insert a specific measure, the dependent computed field will report a "0.00" o "?" value, that is unacceptable.

My idea was to complete the computations by original script, then loop through the field of the same record (i.e. patient) and clean it up. The solution you have presented here looks really promising and I will work on it in the PM, but if you have more efficient ways to accomplish the same result would be great ! 

 

For now, and always, thanks a million to everybody : )

 

P.S: Eos and Lee, I have worked on my profile, hope that works but I am ready to do more as you suggest !

[comment]

Of course, when the user does not insert a specific measure, the dependent computed field will report a "0.00" o "?" value, that is unacceptable.

 

Could you clarify what exactly is "unacceptable"?

 

a. The user has not filled the required field/s;

b. The calculation is performed when some of the referenced fields are empty;

c. The result is 0 or error;

d. The result of 0 or error is being displayed.

 

These four are not quite the same.

 

 

Also, is there a reason why these calculations are performed by a script, instead of using calculation fields?

 

 

---

EDIT: added option (d).

Edited September 20, 2013 by comment

[messadua]

Hi comment, and thanks for the clarification request.

 

It is unacceptable (in other words, unpleasant) that the field reports 0 or "?". Of course  the user cannot fill all the fields (there are measurements included only for patients with specific issues) but since the numbers in the database  will be ultimately used for statistical analysis, the dataset need to be as much "clean" as possible.

 

I could also use calculations fields but for several reasons I prefer to use scripts whenever possible (since I have no time to work on the databsase full time,it is easier for me to debug and remmeber what I have done since last "release"). BTW, what would be the additional benefit of setting these fields as calculations istead of scripting everything up ?

[comment]

what would be the additional benefit of setting these fields as calculations istead of scripting everything up ?

 

Well, for one thing, calculation fields re-calculate automatically when a referenced field is modified - so that you do not need to run a script whenever you correct a data-entry mistake. Conversely, you don't need to worry about accidentally editing a calculation field.

Lastly - and perhaps most importantly in this case - calculation fields have an option (turned on by default) of "Do not evaluate if all referenced fields are empty".

 

 

 

It is unacceptable (in other words, unpleasant) that the field reports 0 or "?".

 

So perhaps it might be sufficient to conditionally format the field to hide such result (assuming, of course, that it's impossible for the calculation to return a legitimate 0 based on valid user entry)? Not that I would recommend this - I firmly believe in eliminating a mess in the first place, rather that cleaning it up or sweeping it under the carpet afterwards.

 

 

 

there are measurements included only for patients with specific issues

 

Are these issues recorded somewhere? If yes, why cannot your script skip the non-applicable areas (as in "eliminating the mess in the first place")?

[messadua]

Ok !

I have got your point Comment, and I am thinking to modify the computation fields as real calculations, although the database will be ultimately linked to a MySQL/PHP environment and I am not completely sure how  calculation fields would react to that. Anyhow, here we go ! I have been working on your suggestions on how to loop through fields  (in order to fix the issue of course, but also to learn something ) and finally got a working script. The script is using a temporary layout where I copied all the computations subject to validation, and I had to do that because unfortunately, I am still not able (i.e. not capable) to use your suggestions on Set Field by Name command. In particular, I am not able to insert a calculation in the command. In other words, after having obtained the value of the field by GetField () I am not able to set the command to complete a computation Set Field by Name [$FieldValue ; if $FieldValue = 0 --> do something, Otherwise, do something else ]. Second issue being how to Loop the values in the list (i.e. array) I have created with List (Field1 ; Field2 ; Field3...). At this point I have a working solution and I am happy with that, still would like to learn how the script could be improved. I am attaching here a screenshot of my script, any commenst would be greatly appreciated ! Thanks a lot to everybody for the huge help, this place is fantastic

[comment]

IMPORTANT!
Your script will destroy your existing field contents. You are pre-computing your result based on the contents of the field in the current record - then using Replace Field Contents to populate all records with the computed result.

What you should be doing instead, is to replace the field contents with a calculated result (i.e. calculated within the Replace Field Contents[] script step itself).

The calculation formula itself could be simply =

Let (
currValue = GetField ( Get ( ActiveFieldName ) )
;
Case ( currValue ≠ 0 and currValue ≠ "?" ; currValue )
)

I may add a few notes re your script later, but this is the most salient point here.

[messadua]

Yes Comment, you are totally right

I have not realized the danger at all and it would have been a disaster. Now, what about using Set Field instead of Replace Field Content, would have that been different ?

 

Thanks a million for the advice, now the script is working as it is supposed to