looping script to copy data from portal

[hociman]

I am attempting to write a script that will copy data from fields in a portal row and paste the contents of each field in the portal row onto a line in a text field.

I have the following script steps that accomplish what I want to do for the first row of a portal.

Go to Portal Row [spots::SpotNumber]

Go to Field [select/perform; Spots::SpotNumber]

Copy [select; Spots::SpotNumber]

Go to Field [Project::ScheduALL Notes]

Paste [No style; Project::ScheduALL Notes]

Insert Text [" "]

Go to Portal Row [spots::SpotNumber]

Go to Field [select/perform; Spots::Title]

Copy [select; Spots::Title]

Go to Field [Project::ScheduALL Notes]

Paste [No style; Project::ScheduALL Notes]

Insert Text [" "]

Go to Portal Row [spots::SpotNumber]

Go to Field [select/perform; Spots::Length]

Copy [select; Spots::Length]

Go to Field [Project::ScheduALL Notes]

Paste [No style; Project::ScheduALL Notes]

Insert Text [" "]

Go to Portal Row [spots::SpotNumber]

Go to Field [select/perform; Spots::Code]

Copy [select; Spots::Code]

Go to Field [Project::ScheduALL Notes]

Paste [No style; Project::ScheduALL Notes]

Insert Text [" "]

What I would like to do is loop the above sequence of steps. The loop would increment the previous SpotNumber by 1, and then proceed through all of the script steps listed above. When all of the populated rows in the portal have been processed with these script steps, the loop would exit. I have tried using some If and Loop (and combination thereof) statements, but have been unsuccessful. If anyone can point me in the right direction for making the above steps loop and subsequently terminate properly, it would be appreciated.

Thanks for reading!

[trev hatchett]

hi hociman!

First of all, I suggest that you use a single calculation which could be something like this:

Set Field [ Project::ScheduALL Notes ;

Spots::SpotNumber & " " &

Spots::Title & " " &

Spots::Length & " " &

Spots::Code ]

to set your "Project::ScheduALL Notes" field from the various fields in your "Spots" table in one hit.

You should find that this is much easier for you to understand and maintain. Better still, it doesn't involve COPY and PASTE, which is not entirely secure, especially if some of the fields are not on the current layout. It also prevents screen flashing which makes for a better user experience.

Your next problem is going through all of your "Spots" records.

A typical method is using Go To Related Record in your script, making sure you check the "Show only related records" option to limit your found set.

Then use Go Record/Request/Page [First] to initialise your loop, and loop by using Go Record/Request/Page [Next] and check the Exit after last option to ensure the loop finishes cleanly.

Hope this solves some of your difficulties.

Trev

[hociman]

Hi Trev,

The calculation makes things much more manageable. Thanks for the tip! Now I've got to work on the loop.

Cheers!

[hociman]

I still cannot get this loop working properly. I suspect I know what is happening, but the real problem is how to get around it.

What I am doing is taking the contents of a portal row, and inserting them into a text field. I then want to go to the next portal row, insert its contents into the same field, and continue until all of the populated portal rows have been processed.

I am trying to use the Go to Portal [Next, exit after last] option, but it keeps going to the first row of the portal. I suspect it goes to the first row because when it leaves the portal to insert data into the text field, its going to the portal from the text field, and since its coming from outside of the portal, the next row is the first row.

If that logic is indeed what is going on, then is there a way to have FMP know what row it was at last, and then go to the row following the last visited row?

Thanks for reading!

Edited December 13, 2005 by Guest

[trev hatchett]

Hi there

It sounds to me as if you're still using copy and paste, which will take you away from the portal to the field into which you are pasting, as you guessed.

This is another good reason for using the Set Field [ ; ] step. You should then be able to go the the next record in the portal without any problem.

However, to make the Set Field [ ; ] step work in your loop, you probably need two things.

First, you should consider making the field you are setting a global. In FMP 7, a global can be accessed without swapping layout or using relationships. That can be very handy.

Second, you need to set up your calculation so that it accumulates information from each record in your portal. Assuming that you have a field "g_Temporary_Accumulator" which has global storage, change your calculation within your loop to be like this:

-----------------------------------------------

Set Field [ g_Temporary_Accumulator ;

g_Temporary_Accumulator & "¶" &

Spots::SpotNumber & " " &

Spots::Title & " " &

Spots::Length & " " &

Spots::Code ]

-----------------------------------------------

Don't forget to set "g_Temporary_Accumulator" to empty before you start your loop, otherwise it could contain all the garbage from a previous run of your script.

And after your loop has finished processing, you can put the contents of your "g_Temporary_Accumulator" field into your "Project::ScheduALL Notes" field like this

-----------------------------------------------

Set Field [ Project::ScheduALL Notes ; g_Temporary_Accumulator ]

Set Field [ g_Temporary_Accumulator ; "" ]

-----------------------------------------------

and clear your gloabal as well, just to be on the safe side.

See if that works for you.

Trev[color:red][color:red]

[Vaughan]

It is sooo much easier to do the process int in the related table rather than through a portal.

Use the Go to Related Records script step to find the related records, then run a looping script through the related table that processes the records, then return to the original layout.

[hociman]

Using Vaughan's approach, here is my script.

--

Go to Related Record [show only related records; From table: "Spots"; Using layout: "spots" (Spots)]

Go to Record/Request/Page [First]

Loop

Go to Field [select/perform; Spots::SpotNumber]

Copy [select; Spots::SpotNumber]

Go to Layout ["Home" (Project)]

Go to Field [Project::ScheduALL Notes]

Paste [No style; Project::ScheduALL Notes]

Go to Related Record [show only related records; From taable: "Spots"; Using layout: "Spots" (Spots)]

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

End Loop

Go to Layout ["Home" (Project)]

--

Now, this script is looping in the sense that it makes it beyond the first record. However, it is not exiting the loop. It is stuck in an infinite loop and I must press Apple-period to make it stop. When I look inside the text field that the script is pasting data into, the data the script grabs from the last record is pasted several times.

So, my question is now this: Why is Exit After Last not exiting the loop? Is this a scripting syntax issue, or something more sinister?

Thank you everyone for your help! I'm almost there!

Edited December 14, 2005 by Guest

[T-Square]

Remove the GTRR inside the Loop. That re-sets the recordset. Set your first GTRR to run in a new window. Then, in the loop, don't go back to the original layout; build your result completely (maybe using a global), and when you finish in the loop, put the complete result in ScheduALL. It should run faster, too.

[Vaughan]

The copy/pastes are not good practice, use Set field instead. That will allow you to avoid having to change layouts insde the loop which may be causing problems.

Go to Related Record [ stuff ]

Go to Record/Request/Page [First]

Loop

Set Field [ whatever ]

Set Field [ whatever ]

Set Field [ whatever ]

Set Field [ whatever ]

Set Field [ whatever ]

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

End Loop

Go to Layout ["Home" (Project)]