Does this table contain that field?

[Rev Zim]

Hi folks,

My first post. My projects are to import a QuickBooks Items table into FileMaker and update from tables provided from 4 vendors, each with three to eight files of varying structures.

I expanded the QB file to include new data available from the source files, stuff we hadn't been tracking before, but will be relevant in the next update to our QB files.

One problem I had was how to determine if the current source file contains a certain field, accounting for the different girls named used by each file for the same field in QB For instance, our Roll Price filed could be called Roll Price, Rollprc or Roll prc. 

In my pseudocode, I had three IFs, one for each field name. So I had to think of a way to determine if the field in question exists. My first thought was to throw an error, catching it by watching for no error.

I call the script with the parameter, the name of the vendor table currently in process.

The variable will have a value of something like this: "ZRUG6229::Roll Prc"

Next I Evaluate the value contained in the field, "ZRUG6229::Roll Prc".

If that field does not exist in the ZRUG6229 table, Evaluate() returns "?". Returning anything other than this, in other words, actual data, tells us the field exists.

Then I set the field in my QB table as appropriate.

This idea kept me up a couple of nights, after searching the net and finding no help, so I thought anyone else having use for this method might as well save a sleepless night or two. I provided a little sample below to illustrate populating the Roll price field in my QB file with values in either the "Roll Prc" or the Ro;;Prc" field, depending on what a particular vendor file calls it.

 

Set Variable [ $checkForRoll Prc; Value:Get ( ScriptParameter ) & "::Roll Prc" ]

If [ Evaluate ( $checkForRoll Prc ) ≠ "?" ]

    Set Field [ items list old::Roll price; Round(Evaluate($checkForRoll Prc) ; 2)

End If

 

Set Variable [ $checkForRollPrc; Value:Get ( ScriptParameter ) & "::RollPrc"

If [ Evaluate ($checkForRollPrc ) ≠ "?" ]

    Set Field [ items list old::Roll price; Round(Evaluate($checkForRollPrc) ; 2) ]

End If

 

Cheers,

Denis

[comment]

Hm.

If you know that the current table contains a field named either "Roll Price" or  "Roll Prc" or "RollPrc", you could determine which one is it by =

FilterValues ( "Roll Price¶Roll Prc¶RollPrc" ; FieldNames ( "" ; Get ( LayoutTableName ) ) )

This is assuming the field is present on the current layout; otherwise you would use =

ExecuteSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName=?" ; "" ; "" ; Get ( LayoutTableName ) )

to get the field names.

You could then use the GetField() function to get data from the appropriate field.

 

Even your method would be more elegant, if not more efficient, if you used =

IsValid ( GetField ( $testFieldName ) )

as your test.

 

However, it is difficult to imagine a situation where the developer writing the script would not know the names of the fields in the solution he or she is working on.

 

 

Edited May 28, 2019 by comment

[Rev Zim]

Excellent, and more elegant, solutions to my little problem. Thanks for the lesson.

I fooled with your suggestions and really like them.  Once I've got this project about wrapped up, I will certainly use variations on them.

Thanks very much.

[Rev Zim]

Hi folks,

Having reread the topic description, I realize that my post should have been elsewhere. I shall endeavor to post to more appropriate forums in the future.

Rev Zim