Gathering records from Relationship (Best practice)

[Ron Neville]

Hi Guys 

Been developing with Filemaker since version 6 and stopped at 12. Now with 15 there are many better ways to achieve results.

I have an Invoice table and the products table have sub components. E.g a Laptop has a cable and carry bag as sub components.

In the old days after adding the main item I would go to the sub components table via a relationship and loop through the records adding them to the order. Just wondering if there is a better way now with V15.

Cheers Ron

 

 

[Wim Decorte]

You can avoid the context switch and the looping by collecting the sub components through an ExecuteSQL() query, collect the IDs at a minimum but you could collect all the relevant data, then loop through that eSQL result instead of looping through physical records in the sub table.

[Ron Neville]

Thank you so much Wim... Could you please give me help on SQL script and one more question.. How do I get the info out. Say I get the list of ID's added to a variable. Extracting each ID i have no idea about achieving that.. Thank you so much Ron

[Wim Decorte]

Once you have all the IDs in a variable it is as simple as using GetValue() in a loop to extract each one.  ValueCount() will give you the max number of iterations.

[Ron Neville]

Hi Wim 

Thank you and am getting there.. I have what I need in a variable $$SUBCOMPONENTS which has come from a SQL query and is SKU and the quantity:

ST0001    1
9315999174634    1
SPA941    1 

Could you please tell me how I can loop thru and get valueOne and valueTwo extracted individually? $FoundCount is 3 so will need to loop through adding 3 new records and adding SKU and Quantity.

Thank you so much 

[Wim Decorte]

Not sure what field delimiter you used in your SQL query there; a tab?  Better to stick with the default (comma) here.

In your loop that runs 3 times, each iteration would do this (say that $x is the variable to keep count of the iterations until you reach $FoundCount)

 

Set Variable[ $line ; GetValue( $$SUBCOMPONENTS ; $x ) ]

# then split it by the field delimiter

Set Variable[ $split ; Substitute( $line ; "<field delimiter>" ; "¶" )]

Set Variable[ $sku ; GetValue( $split ; 1 ) ]

Set Variable[ $qty ; GetValue( $split ;  2 ) ]

 

Note that you do not need to use a global variable ($$) if you collect that data in the same script.  And even if you do collect it in another script, just pass it as a parameter to the script that processes it.

http://www.soliantconsulting.com/blog/2014/02/all-variables-should-be-global-or-not

[bcooney]

Had some time for a demo of how I'd go about it. I would keep subcomponents in the same table as products. I would either go multi-key on id_parent_product or add a join table if you'd like to relate the same subcomponent to more than one product.

Anyway, the point is to show how to use esql and loops.

Also, to avoid changing context I'd have the line items created PSoS.

hth,

Barbara

ron_demo.fmp12.zip

[Ron Neville]

I love the filemaker community and sincerely thank Wim and Barbara for their help...