Script not setting the field in one table with data from another table

[JMW]

I'm trying to compare part of one string to another.

If the sub string matches the string then I need to copy the item code for that lens from the table it resides in to a line item on a transaction.

 

 

An example of the data for the code I've written:

 

Items::CL_Include[1] = Focus Dailies 8.6

Items::CL_Include[2] = 

Items::CL_Include[3] =

Items::CL_Include[4] =

 

Items::Item Code = c8

 

Trans::ContactLensR = Focus Dailies 8.6 some contact Rx info here

 

 

 

The problem I'm having is that SET FIELD doesn't copy the data from the Code::Item Code to Trans::St CL store OU field

 

Can someone tell me what I'm doing wrong?

 

 

The existing script uses SET FIELD [ Case( hardcoded partial contact lens names; hardcoded item code) ]

This means contact lenses and their codes cannot be modified without modifying the script.  

When users modify the names and codes it breaks the script.

I'm trying to rewrite the script to eliminate the hard coded stuff in the current script.

 

 

If I Items::CL_Include[x] matches a substring of $contact lens then I know I have a match and need to copy the contact lens item code to a field in the transaction file.

 

 

Below is the my script:

 

 

Set Variable [$contactLens; Value:Trans::ContactLensR]  //see above for example of data for this field

 

New Window [Name: "Contact Lens Pull Down Data"; Height: 750; Width: 750; Top: 50; Left: 50 ]

 

* my layout displays all four fields in the repeating field CL_Include and the corresponding Item Code from the Items table.

* these are displayed in a portal on the layout

 

Go to Layout ["Contact Lens Pull Down String" (Trans)]

 

Enter Find Mode [Restore]

Perform Find [Restore]

Enter Browse Mode[]

 

Set Variable [$numRecords; Value:Get ( FoundCount )]

 

Go To Record/Request/Page [First]

 

Set Variable [$numRecordsSearched; Value:1]

Set Variable [$match; Value:"False"]

Loop

   Set Variable [$wordCount; Value:WordCount( Items::CL_Include[1] )]  

   If [LeftWords( Items::CL_include[1]; $wordCount ) = LeftWords( $contactLens; $wordCount )]

      Set Field [Trans::St CL store OU; Items::Item Code] *** problem here ***

      Set Variable [$match; Value:"True"]

   End If

   Exit Loop If[$match = "True" or $numRecords = $numRecordsSearched ]

   Go to Record/Record/Page [Next]

   Set Variable [$numRecordsSearched; Value:$numRecordsSearched + 1

End Loop

 

I'm a programmer but I have very little database experience.

 

After looking at lots of posts on here I think I can simplify my Loop but my concern at this point is that the Set Field is not working and I need to be able to assign Trans::St CL store OU the value from Items::Item Code so it can become a line item on a transaction.

 

Thanks for your help.

[GisMo]

Do you have the script debugger? If so, You can see the error code when you use set field. Most set field issues are context based. Make sure the current active layout is of the correct table/context for you to write the data to that field. Also, it would be helpful to see your database diagram as well or simplify your example and remove the jargon.

It also seems like you may be able to resolve this same issue via a new table relationship and a simpler script.

Edited August 21, 2014 by Lee Smith
Removed the Quote of the whole post that was not needed in this reply.

[bruceR]

Your script is very hard to follow. Among the problems:

enter did mode [restore]

Perform find [restore]

These two statements conflict. Much better and more readable, use explicit scripted finds:

Enter Find Mode ( no restore)

Set field [ someField; someValue]

Perform Find ( no restore)

[bruceR]

Also, I think you'll have to capture the code into a variable before switching layouts.

 

Set Variable [ $itemCode; Items::Item Code ]

-- do stuff

​-- switch layout etc

Set Field [Trans::St CL store OU; $itemCode]

[JMW]

GisMo,

No I do not have a copy of Filemaker Advanced.  I think it's time I ask for one though.

I tried creating a table relationship but it didn't seem to work.

 

Bruce,

Sorry my script is hard to follow.  I'm learning that Filemaker will do some of the loop control for me.  I come from non-database coding background and am not used to these features.

 

Thanks for pointing out the conflict with the Enter Find Mode [restore] and Perform Find[restore]

It turns out that was part of my problem.

 

I can't see how I can use a Set Field for the find.  I'm trying to find all CL_Include[1] fields that aren't blank, so I end up with a list of found records.  I then compare the data in the CL_Include fields to part of a string indicating which contacts a patient wears.  When I find a match, I copy data in the CL_Include record to a line item on a transaction.  The problem is that the data in the CL_Include field becomes my search string and I have to cycle through each of them until I find a match.

 

I also learned that script I'm rewriting is so old it was written before Filemaker had variables.

So, instead of using SET FIELD to pass a value to the calling script I used a variable to return the value.  Is this considered good programming in Filemaker?

 

Thanks for your help.