ComponentLevel The production of the ready-to-evaluate array of name/value pairs could, I think, be reduced to a recursive custom function.

Learn how your comment data is processed. function. I used the latter. That said, I retained the SQL concatenation of all fields instead of This information can be useful for reporting or product recall scenarios. That result is used as the parameter in the SetLocalVars CF to instantiate all the row’s name/value pairs as $Name variables.            @AssemblyID INT As a basic example, we will consider the manufacture of bicycles. I’ve seen several examples of developers inserting a unique string of characters as a field separator, and using native functions or custom functions (CFs) to tease out the individual fields’ values from a row. SQL Accounting System(E)-SST- Upgrade and Important Setting for SST User - Duration: ... Bill of Materials Software - ROBO Bill of Materials - Duration: 4:27.                     ComponentLevel + 1 This is key to how Virtual List identifies the value for each record. An error occurred, please try again later, Play Exploding Bill of Materials using Graph Shortest Path. Thanks, that is great. I have a CF (named “#” for easy typing) that takes two parameters: # ( “Name” ; “Value” ) and prepares them for instantiation as $Name ( = Value ) when passed as the parameter to another CF (named setLocalVars). -- MSSQL find all components needed for an assembly …which is paired as the Value (with the Name “children”) and passed as one of several formatted name/value pairs to a subscript. INNER JOIN BOMCost AS bc ON bqd.TRX_ID = bc.Assembly. It also includes a quantity (Qty) field that identifies the number of child units required to make one parent unit. GO -- Recursive part - recursive CTE Note that this will only return you the shortest path between P1 and P2. It returns that list as the value of the “retArray” name/value pair, which is captured via script result and appended to the current $array variable. Editor’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. CREATE PROC AssemblyComposition 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.                     b.PerAssemblyQty, FileMaker uses the “&” to concatenate, while SQL can use “+” or “||”. Creating a bill of the material requires knowledge of that sector. I’ll probably use something similar in the future. SQL uses single quotes to identify text strings that will be concatenated with values returned by the SELECT parameter.

As a relative newcomer to SQL queries in FMP, I was pleased to discover how much power they offer developers in situations like this.     END insert some of the text strings with less typing or cut/paste. I’ve only sparingly used ExecuteSQL in projects until recently. The subscript tracks the total iterations through the loop, and adds the current iteration number as a suffix to the Name parameter of each name/value pair. 820         HL Road Front Wheel           330.06     WITH cteAssembly(AssemblyID,ComponentID,PerAssemblyQty,ComponentLevel) Great instance of a multi-level BOM, Geoff!

Is it possible to get a list of all products effected by shortage of a specific part? -- Execute stored procedure with recursive tree processing ... 01 Example Queries.sql.              a.ComponentID, They have no additional impact on the Relationship Graph. /* Creating rows of name/value pairs to pass in a script parameter seemed ideal, since the script could then instantiate a row in each iteration via a loop. ProductID   ProductName                   ListPrice I personally have benefited enormously from his expertise, and am pleased to feature his first appearance as a guest author here on FileMaker Hacks.              FROM   Production.BillOfMaterials AS bom Great work! It also means there’s no need for a “dictionary” type CF to make use of named values, and there’s no dependence on the order (sequential positioning) of the passed name/value pairs. Your solution works even better, as mine does not support escaped quotes (\”). (Work Days subtracts weekend days within the date range thanks to a calc by Ray Cologon.).              INNER JOIN Production.Product AS p2 The Evaluate () function, with one row as its parameter, produces a concatenated string of formatted name/value pairs. -- Microsoft SQL Server stored procedure with recursive TREE processing-- MSSQL find all components needed for an assembly-- T-SQL bill of materials processing USE AdventureWorks; GO CREATE PROC AssemblyComposition @AssemblyID INT AS BEGIN WITH cteAssembly (AssemblyID, ComponentID, PerAssemblyQty, ComponentLevel) -- Root (anchor) part