Jump to content
Sign in to follow this  
yoroshiku

Target & delete a record in another table

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 ?
 

Screen Shot 2019-03-10 at 19.49.53.png

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Steve Martino

Share this post


Link to post
Share on other sites

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.
 

Share this post


Link to post
Share on other sites

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

Screen Shot 2019-03-10 at 22.29.19.png

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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