setting multiple portal rows into a new portal

[Chemadian]

Hey Everyone,

What i am trying to do is pre-fill a new record with a bunch of fields from a previous record. I'm going about this by setting variable's and then "set field" to fill in the designated fields in the new record.

The problem i am having is that i need to do this with a field in a portal and using the above method will only fill in the first row of the new portal.

I'm looking for a way to grab multiple rows of the portal instead of just the first one to pre-fill the new record. I'm sure this is easy for you filemaker experts and any help is appreciated!

[efen]

Goto portal row [last] or [by calculation]

[Chemadian]

I might need a bit more explanation or an example. As i understand Goto Portal row [last] won't allow me to set multiple portal rows would it be more like...

Go to portal row [First]

set variable [ ]

Go to portal row [next]

set variable [ ]

go to portal row [next]

set variable [ ]

...and so on.

As for [by calculation], which i think would be the better option, can you give an example of a calculation that will allow me to set as many portal rows that have text in them?

[Vaughan]

Starting on the parent record, remember the primary key, go to a layout based on the related records, create a new record, set the primary key and other fields, then return to the original layout. Even better, do all this in a new off-screen window so the state of the original layout is not disturbed.

[Chemadian]

Vaughan,

Not really sure what that tells me about my original question. I'm looking for some advice on how to set multiple portal rows, hopefully through some sort of calculation, not how to create a related record.

[bcooney]

I think that this is a case of asking for a technique without explaining why your design needs this. Vaughan describes how to "copy" portal rows which are, of course, related child records of the parent, by operating in the child table offscreen.

I'd like to understand a bit more about your goal. Why does a new record so closely resemble an existing record? Can you describe the data. Perhaps there's a better way to set your default values.

[Chemadian]

bcooney,

The database i have built is meant to store chemical formula's, which can be thought of like recipes. Each "recipe" is made up of multiple ingredients (displayed in a portal). We have ten's some times hundreds of very similar recipe's that often just vary in the amount of each ingredient used. So in order to assist the user in creating these similar recipes i thought it would be a good idea to pre-fill the fields that stay the same. So for instance...

Recipe #1

Ingredients Amount

Ingredient #1 ----> 20 units

Ingredient #2 ----> 30 units

Ingredient #3 ----> 10 units

Ingredient #4 ----> 50 units

Recipe #2

Ingredients Amounts

Ingredient #1 ----> 15 units

Ingredient #2 ----> 20 units

Ingredient #3 ----> 5 units

Ingredient #4 ----> 40 units

So between the first and second recipe all of the ingredients stay the same its just the amounts that change, thats why i am looking to grab each of the 4 ingredients and set them into the portal of the new record.

Hopefully this additional information helps,

Cheers!

[bcooney]

Thank you, that does help. So, you are duplicating a parent and its children. See Ray Cologon's demo.

However, I find that editing default data is more difficult for a user than starting with a blank slate. I would design an interface that has a table of recipes on the left side in a portal (perhaps with a filter by some key word or type field). User selects a recipe and a portal of ingredients shows in a second portal. Then, user can add an ingredient to the current recipe's ingredient portal by selecting an ingredient from the second portal as well as a portal of all available products.

Another design would be to copy the parent and its children, but require that each child record is approved. If not approved, then the portal row is highlighted yellow. That'll help them know if they've confirmed the ingredients are correct.

[comment]

i am looking to grab each of the 4 ingredients and set them into the portal of the new record.

Finally, something I understand... See if this helps:

DuplicateParent+.zip

[Chemadian]

bcooney,

Thank you for your help! The layout that i use is actually set up exactly the way you suggested, having one portal display all of the recipes and a second portal displaying the particular ingredients for each recipe so that gives me some confidence that i'm on the right track

Comment,

The file you attached is exactly what i need and it makes perfect sense to use the looping function! The problem i am having now is that the portal i need to grab the ingredients from is populated by a value list....and i'm not sure what script steps i would have to change to make that work with a value list. It might very well be a simple tweak that i am over thinking but if you have any further advice on doing that same idea but with a value list that would be perfect!

cheers

[comment]

The problem i am having now is that the portal i need to grab the ingredients from is populated by a value list

Perhaps I am missing something, but I don't see what difference it makes.

[Chemadian]

Hey Comment,

Thinking about it now, your right it wouldn't make a difference with or without a value list. I assumed it was something with a value list because this script isn't working for me...it looks something like this

If [Formula LIne Items::_FK_FormulaID]

Go to Related Record [ Show only related Records; From table: "formula Line Items"; Using layout: "formula Line items" (formula Line items)]

Go to Record/Request/Page [first]

loop

Set variable [$IngredName; Value:Formula Line Items::Ingredients]

Go to Record/Request/Page [Next; Exit after last]

End Loop

End If

Go to layout ["Audit Form" (Audit Form)]

New Record/Request

Loop

Set Variable [$i; value: $i +1]

Exit loop if [$i > ValueCount (Formula Line Items::Ingredients)]

New Record/Request

Set Field [Formula Line Items::Ingredients; $IngredName]

End Loop

I'm still unfamiliar with scripts so i don't understand why i need the "IF" script step when gathering the child records. Any further explanation is appreciated!

[comment]

The If [] step checks for existence of related records.

This part doesn't look right;

Set variable [$IngredName; Value:Formula Line Items::Ingredients]

[Chemadian]

The "value:" should be the join table between the children and the parent should it not? Also thanks for the explanation on the If [] statement.

[comment]

There is no such thing as a "join table between the children and the parent". A join table is a child of two parents.

Anyway, the point is that during the loop the variable needs to assemble the individual values into a return-separated list. The way you have it, you are replacing the value at each iteration, instead of adding to what you already have.

Also, if 'Formula Line Items' is a join table, it should only have a field for IngredientID. The name of the ingredient should be in the Ingredients table.

[Chemadian]

Comment, ya bad description on my part. Your exactly right about my problem of replacing the value at each iteration.

another problem i had was Exit loop if [$i > ValueCount (Formula Line Items::Ingredients)] should have been Exit loop if [$i > ValueCount ($IngredName)]

Thanks for your help