Adding Check box to portal

[H]

Hi

I am developing a food Production database.

i have a table for the products and another table for the ingredients - all working great.

Now i am creating a production table , the way it works there is a production to a product at a certain date.

The Ingredients to the product get fed in through a portal.

What i need is that the production overseer at the manufacturing plant should be able to check every ingredient of the product if is approved to there standard and is available in manufacturing plant prior to production.

The approval are Production to production basis not ingredient basis, meaning on every new production i want to be able to check again if that particular ingredeint is already approved.

How could this be created  as to every product there are a different amount of ingredients and i would only like a checkbox to appear near the portal if their is that ingredient in that portal row.(see attached image)

 

Another issue i experience is that every couple of month we change the manufacturers from where the ingredients come from or we slightly change the ingredient list  all works fine from the product and ingredeitn side , however the ingredeint in this portal changes. how can i create that the information should stay as of when it was created.

i would appreciate some advice to my queries.

[H]

Anyone has any solution for me?

[Fitch]

I think you got no response because your description is a little hard to understand, and the screenshot doesn't make it any clearer, at least to me.

That said, it sounds like what you need is another table or possibly two tables: Production Product (PP) and Production Ingredient (PI). The idea is that you'd create a record in PP for a given production run, and for each PP record you'd create the corresponding PI records. These records would include any additional fields such as the production date, manufacturer, approval, etc. These records would link to the existing Product and Ingredient tables by ID, but would exist independently so they'd retain their data even when the existing tables are updated.

HTH

[H]

Thanks for your response , you got exactly what i wanted and i was taking your route.

I had created a PP Table and that was working fine , i could create as many productions to one product , however when it came to the ingredeint that is where i was going wrong.

the reason is that a new production would be created manually by pressing on "New production" , whereas the ingredients to that production would need to be brought in from the ingredeint to the product.

You solution of creating a PI table sounds logical , but how would the data be copied over then, please if you don`t mind to explain in more detail how i could yet link but keep separate meaning if an ingredeint in the product changes it shouldn`t reflect in a past production.

i would appreciate some support , thanks.

[Fitch]

I would probably script: grab a list of the related ingredient IDs; then either on the PI layout or via a portal from PP, loop through that list and create a new record for each value.

[H]

This solution sounds perfect for me  , i am just a little new with these complex scripts, (been using basic for month now - getting better and better) 

How would i grab the list of id`s , i can only get one script parameter or would i do that through variables?

i would appreciate if you guide me a little closer with the script steps of grabbing it and then creating it into records.

I hope one could help me

[Fitch]

You'll need to adjust this to reflect your relationships and layouts, but the script would be something like:

// assumes you have a portal to PI on the PP layout
// with 'allow create' ON
Set Variable[$productID; product::ID]
Set Variable[$ingredientList; List(ingredients::ID)]
Go to layout[PP]
New record
Set Field[PP::productID;$productID]
Set Variable[$ingCount; Count($ingredientList)]
Set Variable[$i;0]
Loop
 Set Variable[$i;$i+1]
 Exit Loop If[$i>$ingCount]
 Go to Portal Row[Last]
 Set Field[PI::ingredientID; GetValue($ingredientList;$i)]
End Loop

 

[H]

Fitch , thanks so much for your detailed script , i am studying it and try to adjust it accordingly.

One question, how do i 

On 6/15/2016 at 8:50 PM, Fitch said:

// with 'allow create' ON

I think that is where i am going wrong as i am not getting resultys expected

[webko]

He means that the PI relationship needs to be set in the Relationship Graph to Allow Creation of records. so that setting the Field in the last step above works...

 

[H]

Hi  ,thanks again all for your help

Experiencing another issue with the above script

On 6/15/2016 at 8:50 PM, Fitch said:

Set Variable[$ingCount; Count($ingredientList)]

 unfortanately doesn`t count.
 

see my full script below

Any idea why the count doesn`t work.  If i replace the variebale with a number one record is displayed thats in , no diffence how many ingredients there are.

[Fitch]

I said Count but it should be ValueCount. Sorry about that. I've corrected my post above.

[H]

Ok thanks  , that issue sorted now - great - couldn`t get my head round it where was going wrong.

One more issue now  - it counts it correctly , but only the first record gets created , any idea why?

[Fitch]

Can you verify that $ingredientsList variable that you set with the List function? Are you using the right relationship? Maybe your list only has one value.

To test this, try adding a Show Custom Dialog step after you set the variable, and set the message to $ingredientsList.

[H]

Did that , $ingredientsList is working fine , i guess there is some error in the looping that it breaks up after creating one record - can`t seem to figure it out... any idea what could f gone wrong?

[webko]

Personally, I would go to the child table and create the records there. Set the parent ID into a variable, then go to a layout for the child records, and loop to create new with the ID and the relevant $ingredientsList value.

And stump up for a copy of Advanced - three seconds with the Script Debugger would pinpoint where the issue is in this case...

[Fitch]

I agree with webko's solution, but it would be nice to find out why this isn't working as it should.

Can you verify that $ingcount is greater than 1? How about adding a Pause in your Loop after you go to portal row and/or after you set the field?

[H]

13 hours ago, Fitch said:

Can you verify that $ingcount is greater than 1

Yes  , because i added a script that it should set a fild with that number 

13 hours ago, Fitch said:

How about adding a Pause in your Loop after you go to portal row and/or after you set the field?

What would this pause do , how would it help it out?

[Fitch]

The pause would help troubleshoot because you could see if the script is going to the row but not setting the field, or what. I'd add a Pause after every line in that Loop.

This is why FileMaker Advanced is so useful -- it has a script debugger so you can step through a script line by line, as well as observe the values of your variables in the data viewer. But without Advanced, you have to use workarounds like adding pause steps.