Jump to content

Gathering records from Relationship (Best practice)

This topic is 2783 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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



Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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.




Link to comment
Share on other sites

This topic is 2783 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.