Filemaker pro 18 templates full#

The example includes a Days field that identifies the lead time to receive a purchase order for that item (or to produce an Assembly Item) once all descendant material and Assembly Items are on hand. That subscript instantiates the list as $children and walks through it, instantiating one row of variables with each loop iteration, and recursively calling itself every time the targeted row is an Assembly Item.

I use that list as the Value parameter in the name/value pair function with the Name parameter “children” and use the result (concatenated with other name/value pairs) as the script parameter passed to the “SubMakeBOMList” subscript. I leveraged #2 in the SELECT segment of the ExecuteSQL function to produce a response that is a list of ready-to-evaluate name/value pair functions that will supply the relevant values and attributes of each matching record as the script progresses. I use a name/value pair method for passing and returning multiple parameters to and from scripts. You can (via JOIN) include data in the response rows that comes from an on-the-fly relationship that otherwise doesn’t exist on the RG.You can add prefix and suffix text strings to each value in each record/row returned by the query.They have no additional impact on the Relationship Graph.As a relative newcomer to SQL queries in FMP, I was pleased to discover how much power they offer developers in situations like this. The example file uses $variables in place of global fields, SQL instead of multiple relationships, and a recursive script framework that can handle an unlimited number of generations in the multi-level BoM. Some Assembly Items can be purchased ready-made, so we identify the “family tree” of Assembly Items that should be purchased when there are not enough days between the “Start On” and the “Targeted Completion” dates to produce the assembly in-house. Because theirs is a just-in-time operation, they wanted to identify material requirements with two parameters: how many units of each material are needed, and when should they order each raw material (and begin work on each Assembly Item) to meet the scheduled completion date of the parent Item. I recently had a chance to revisit the process for a client that makes products with deep multi-level BoMs. The depth of the hierarchy was limited by the need for separate utility relationships, fields, and script steps at each descending level while walking the lists of materials and subassemblies required by a parent Assembly Item. The overhead in FM 3 was not trivial: multiple global fields, multiple relationships, and lots of similar-but-not-identical script steps were required to facilitate the process. This arrangement is often called an “exploded” or “multi-level” BoM that might look like this:Īdding a parent Assembly Item to Inventory requires sufficient quantities of all descendant materials and Assembly Items, which requires walking the entire multi-level BoM to ensure that sufficient quantities of each material is available to produce the required quantity of that material’s parent Assembly Item.

Filemaker pro 18 templates full#

The inclusion of other Assembly Items (which might also contain Assembly Items) means that a full Bill Of Materials (BoM) for a “parent” Assembly Item is best represented as a hierarchical list that clarifies the relationships between each component and its “ancestor” and “descendant” Items.

I personally have benefited enormously from his expertise, and am pleased to feature his first appearance as a guest author here on FileMaker Hacks.īack when FileMaker 3 was new, I added inventory management to a FileMaker template that allowed an Assembly Item to be a collection of other Items, including other Assembly Items. See comments for details.Įditor’s note: Geoff Gerhard of Creative Solutions is one of those “quiet” developers who has been helpfully sharing his knowledge in online FileMaker forums for more than 20 years.