For those of you hanging on every word I type, this is the second part of my discourse on the new While ( ) function introduced with the release of the FileMaker 18 Platform. If you have no idea what I’m talking about, please take a break and read While I Was Away: The First Part before you continue below.
In that first post, we discussed the syntax of this new function and how it is both similar and different to the syntax used with the Let ( ) function. I also related my initial experience when replacing recursive custom functions with their While ( ) equivalent and the potential performance improvement with such a change. I had also promised a few more examples of the While ( ) function in action. Since my mother taught me to never make a promise I couldn’t keep, we find ourselves in the midst of The Second Part.
To begin, let’s take a look at an application that has nothing to do with recursion. On a number of projects, I have been asked to assemble an automated email message containing the details for the customer’s order or shipment without attaching a PDF or similar document. This means pulling the information for the line items from the related table and inserting them into the message template so that the email can remain a simple pure text message.
Like any task in FileMaker, there are a number of ways to accomplish it. The first step in my “go to” method involves the creation of a calculation field in the table containing the related Line Items which I creatively name LineItemCalc. This new calculation assembles the individual pieces of the information for the given line item in the proper order and then inserts the desired separator. In the example involving a customer’s order, the calculation could look like this:
Let ( [ tab = Char ( 9 ) ; itmQty = Quantity ; itmNum = PartNum ; itmDesc = Description ; itmCost = "$" & Price ; itmExtd = "$" & PriceExtCalc ; itmList = List ( itmQty ; itmNum ; itmDesc ; itmCost ; itmExtd ) ] ; Substitute ( itmList ; "¶" ; tab ) )
This calculation grabs the quantity, the part number, the description, the cost, and the extended cost. It then inserts a tab between each piece of text. With this approach, I also have the option to perform some data validation and/or formatting as each piece of data is pulled from the given fields. I have simplified the calculation slightly here by omitting the validation portion of my calculation so you don’t get lost in the noise as it were.
With this calculation complete, the script is very straightforward. Using the List ( ) function, I am able to grab the contents of my LineItemCalc field for each related line item on the order. I can then insert this blob of text, whether it’s one line item or twenty items, into the email that the system is automatically drafting. Having used this method many times, I know it works. However, I have just added some overhead to my FileMaker solution. A calculation field now exists whose only purpose is to populate an email when a particular script is executed.
With the While ( ) function in my toolkit, I now have another option. I can build the same email message, but now everything is contained in my “Generate Email” script, which provides several benefits. First, I no longer have to “clutter up” my solution with extraneous fields. Second, if I need to update the message, I no longer need to play the guessing game as to where the change must be made – in a field calculation or in the script. Finally, a user without full access privileges can write a script to perform the task where the inability to add the calculation field would have halted their progress previously.
So let me break down this new calculation, starting with the initial variable declarations found between the first set of square brackets.
tab = Char ( 9 ) ; index = 0 ; ordItems = "" ; itmCnt = ValueCount ( List ( order_ITEM::__ItemId ) )
With the above, I’ve set my separator character, set my counter for the loop, initialized my text blob, and gotten the total number of items on the given order. I am still using the List ( ) function here, but I’m not pulling any fields values. It is used only to get the count of related line items.
The next part of the calculation defines the conditional statement, or exit condition, for the repeated logic.
index < itmCnt
It is worth reiterating that the conditional statement is evaluated before the repeated logic is executed. Therefore, you need to make sure the statement is written in such a way that ensures the logic is repeated the proper number of times. In this example, I want to make sure the logic is executed while the item counter remains less than the total number of items on the order. In other words, the logic is repeated once for each line item that appears on the order.
In the next section of square brackets, we find the repeated logic, which is very similar to my original calculation. The context has changed since I am now running the calculation in a script from the context of the ORDER table rather than directly from the context of the ITEM table. Please note that I do use the anchor-buoy method in my relationship graph so ORDER is the Order table occurrence and order_ITEM is the T.O. for the related line items.
index = index + 1 ; itmQty = GetNthRecord ( order_ITEM::Quantity ; index ) ; itmNum = GetNthRecord ( order_ITEM::PartNum ; index ) ; itmDesc = GetNthRecord ( order_item_PRODUCT::Description ; index ) ; itmCost = "$" & GetNthRecord ( order_ITEM::Price ; index ) ; itmExtd = "$" & GetNthRecord ( order_ITEM::PriceExtCalc ; index ) ; itmList = List ( itmQty ; itmNum ; itmDesc ; itmCost ; itmExtd ) ; itmLine = Substitute ( itmList ; "¶" ; tab ) ; ordItems = List ( ordItems ; itmLine )
The first line of repeated logic sets the counter for the given repetition of the logic. Then, GetNthRecord ( ) is used to grab the field value for the related record based on the counter value. The individual line item is assembled with the separator character before being concatenated with any results from previous iterations of the repeated logic.
At this point, the result can be inserted into the email template in the same manner I used previously. (I didn’t show this earlier. You’ll just have to take my word for it.) With the While ( ) function, we have another option. Instead of using the “result” in another Set Variable or Set Field script step, we can insert a Let ( ) function as the “result”, or final part, of the While ( ) function and draft the entire email in a single script step. This “result” could then look as follows:
Let ( [ fName = order_BILL::NameFirst ; shipTo = List ( "Attn: " & order_SHIP::zyc_NameFullFML ; ORDER::ShipName ; ORDER::ShipAddress1 ; ORDER::ShipCity & ", " & ORDER::ShipState & " " & ORDER::ShipZipCode ) ] ; Substitute ( $$EmailTemplate ; [ "<NAME>" ; fName ] ; [ "<SHIP_TO>" ; shipTo ] ; [ "<ORDER_ITEMS>" ; ordItems ] ) )
In this example, in addition to the list of line items, the email contains the customer’s name and shipping address. The email template text is loaded into a global variable, and a final Substitute ( ) function replaces the corresponding placeholder text found between the “<“ and “>” symbols. The fName and shipTo text strings were calculated as part of the Let ( ) function “result” while the ordItems text string is the output of the repeated logic we discussed above.
I hope you find this example both practical and easy to understand as you begin using the While ( ) function. I know I promised two examples, but I also realized this article is already longer than I expected it to be. I’m sure your surprise is similar to the emotion I felt when watching the end of “John Wick: Chapter 3 – Parabellum” last month. I fully expected to view the end of the trilogy only to learn that Chapter 4 is already in the works. In the same vein, my final While ( ) function example will be found in While I Was Away: The Third Part, coming to this space in a couple of weeks or so.
Until next time,
TW
