WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
In this example, we have two tables called Table A and Table B. Our goal is to take the 6 data fields in a Table A record and make an individual record for each in Table B. This isn’t a “silver bullet” technique but an illustration of how you can use a combination approach to meet your programming needs. In particular, this example is done via a combination of techniques including concatenation, parsing, create new record, get function and a looping script.
SETTING OUR BIG TEXT FIELD
Our first step is to set one text field equal to all the contents of the fields in the tab order and separate each value with a carriage return.
Here at the script steps for this process with a brief description of what we are doing.
Set Field [ Table_A::Big Text; "" ]
This is to initialize the text field for our script by setting it to empty.
Go to Layout [ “Example” (Table_A) ]
This is our first field in the tab order.
Go to Field [ Table_A::Field A ]
This sets the big text field equal to the first field that our cursor is within.
Set Field [ Table_A::Big Text; Get(ActiveFieldContents) ]
The counter is a global number field. We will be counting each field we enter into. This is used to compare how many fields we have been in ... to how many fields we want to be in. When they match, we exit the script.
Set Field [ Table_A::Counter; 1 ]
The process between the Loop and End Loop will repeat until we achieve the requirements of the Exit Loop If script step.
Loop
Go to Next Field
Set Field [ Table_A::Big Text; Table_A::Big Text & "¶" & Get(ActiveFieldContents) ]
Here we are setting the Big Text field equal to itself plus a carriage return ( for the new value) and the contents of the current field.
Set Field [ Table_A::Counter; Table_A::Counter + 1 ]
This is used to set the counter equal to itself plus one. This is how we count each field that we do in the loop.
Exit Loop If [ GetAsNumber(Table_A::Counter) = 6 ]
In this example, we want to do 6 fields in the tab order. If you wanted to do 148, you would set it to 148. FYI... make sure your tab order is set correctly before running the script.
End Loop
Each Loop script step requires an End Loop script step as a matter of proper syntax.
SETTING OUR RELATED RECORDS
Now that we have our big text field ready, we need to create our related values. This is done by setting up a related portal with the Auto Create Related Records option checked. This means the last row in the portal will always be empty, waiting for you to create a related record.
Our script to creating the related records will include the following steps.
Loop
The process between the Loop and End Loop will repeat until we achieve the requirements of the Exit Loop If script step.
Go To Portal Row [ Last ]
This takes us to the last portal row, which is ready for us to create a related record within.
Set Field [ Info, Case( PatternCount(Big Text, "¶") ≥ 1, Left(Big Text, Position(Big Text, "¶", 1, 1)),Big Text)]
Here we are setting the field in the newly created related record equal to the top row of the Big Text field. We do this by looking for the first carriage return and setting the related field equal to everything left of the carriage return. If there is no carriage return left ( which will be the case on the last piece of information ), we set the field equal to the big text field.
Set Field [ Big Text, Case( PatternCount(Big Text, "¶") ≥ 1, Right(Big Text, Length(Big Text) - Position(Big Text, "¶", 1, 1)), "")
Here we are slicing the top row off of the Big Text field because we have already placed it into a related record. We do this by seeing where the first carriage return occurs and setting the field equal to everything to the right of it. If there is no carriage return left ( which will be the case on the last piece of information ), we set the field equal to empty.
Exit Loop If [ IsEmpty( Big Text ) ]
When we have emptied the field, we want to loop to stop.
End Loop
Each Loop script step requires an End Loop script step as a matter of proper syntax.
An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles08/Parse6.zip
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.
© 2008 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
Click Here To See The FileMaker Book (via a blog) homepage! ===================================================================
Posts
