Jump to content

Target & delete a record in another table


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

Recommended Posts

  • Newbies

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.

Link to comment
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.

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

  • Newbies

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.
 

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

This topic is 1844 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.