Target & delete a record in another table

[yoroshiku]

Hi, i'm new here and noobs on FM
Just a few weeks of practice and learning so far.

Ok, my problem : 
From Table A, i want to target a record based on a specific value $name, find the matching record containing that value in table B, then delete that record and come back to A.

I can successfully create a record from Table A to B with this script :

Set Variable [ $name ; Value: Recipe::Recipe_name ]
Set Variable [ $category ; Value: "basic recipe" ]
Set Variable [ $quantity ; Value: Recipe::Quantity ]
Set Variable [ $cost ; Value: Recipe::C_total ]
Go to Layout [ "Main Inventory" (Inventory) ; Animation: None ]
New Record/Request
Set Field [ Inventory::Item_name ; $name ]
Set Field [ Inventory::Category ; $category ]
Set Field [ Inventory::Quantity ; $quantity ]
Set Field [ Inventory::Cost ; $cost ]
Go to Layout [ original layout ; Animation: None ]

But i can't go back to it and delete the record i want. This is the script i'm struggling with :

Set Variable [ $name ; Value: Recipe::Recipe_name ]
Go to Layout [ "Main Inventory" (Inventory) ; Animation: None ]
Go to Portal Row [ Select: On ; First ]
Loop
If  [ Inventory::Item_name ="$name" ]
Delete Record/Request [With dialog: off ]
End If
Go to Portal Row [ Select: On ; Next ; Exit after last: on ]
End Loop
Go to Layout [ "Technical Sheet" (Recipe) ; Animation: None ]

Can anyone explain what i am doing wrong please ?

Basically, i have a Main Inventory with food ingredients listed. And i have multiple recipes - aka Technical Sheets - where i select my ingredients. What i want to do is to make selected recipes become a new ingredient in my Main Inventory. For exemple Tomato Sauce is a recipe, but it can become an ingredient in a Meat Ball recipe.

[Steve Martino]

Seems like your tables and relationships are not correct. 

I don’t understand why you want to create a record and the delete it.

With the  limited information you provide and your explanation at the end seem to indicate 2 different directions.

id guess you would need a table of materials , a join table of ingredients , which combines all the different materials and a table of recipes.  Much like an invoice solution of Invoices, Line Items & Products.

[yoroshiku]

Thanks for your answer. I know this sounds like a snake biting its tails.

I don't want all my recipes to become available as an ingredient, but only selected ones.
Attached is my structure.

Maybe i don't need to delete the record created, but at least i need to update it. Which for me is the same problem, how do i target it and find it in my loop script ?
 

[Steve Martino]

When you want to update it, from the Recipe table, you want to change it in Inventory for all new records that will use that inventory item in the future?

If so you have to be careful that I’d doesn’t trigger calculations and Lookups that trickle thru all you other records and change them.

You don’t need a loop script.  Just grab the name in a variable, open a new window in the Inventory layout, Enter Find mode, set the proper field (Item_name?) to the variable, Perform Find.  When your done editing, commit record, close window.

Thats a simplified explanation of the basic process.

incidentally, your first script is not the way I would create a new record.  You seem to be doing it backwards.  Another simplified version would be a recipe layout would have a portal from the join table Recipe_line.  When creating a recipe, you create portal records (from Recipe_line) which you look up from the Inventory table. If you change a line item for that recipe, it won’t affect all the other records.  However, if you do want to change it, you then would change it in Inventory. 

Edited March 11, 2019 by Steve Martino

[yoroshiku]

Thanks for your help. Yes, i want to update.. so maybe a join table is better ?..
Now I'm thinking of a "Merged Inventory" table, gathering all data from "Inventory" & "Recipe".. then i can link this join table to "Recipe_Line", as it's my portal. The trick would be to filter the only concerned recipes with Filter Portal Records in my Portal Setup.

Would this work, at the expense of double my database somehow ?
 

Quote

You don’t need a loop script.  Just grab the name in a variable, open a new window in the Inventory layout, Enter Find mode, set the proper field (Item_name?) to the variable, Perform Find.  When your done editing, commit record, close window.

I'll give a try at this tomorrow, i don't know how Perform Find works.
 

[yoroshiku]

I saw your edit and didn't understand everything... BUT i successfully built my target & delete script !! Maybe it's dirty, but it works.
See attached pic

Time to sleep now
Thanks again for your help