Was so proud that i didnt need help for so long, but now HELP!

[WSaxton]

I know this sounds silly but I’m having trouble grasping a loop function for what i am doing.

I have written a script that imports from excel to a specific table works perfect , imports all the data I need as individual records , Great awesome. Here is the problem I cannot get them to relate; when the script starts I create a variable and copy my id for the parent record (Works no issues)

I go to my separate table and request a new record (Works no issues) I set my field and deliver the id to the record (works no issues ) I import from excel ( Works no issues). This is where I have the problem I can’t seem to get the script to insert the id into the imported records. Everything I have read says to loop but I don’t understand how, I understand the theory behind it that you want to go back to the first record imported and then past the id into , then move to the next so on and so forth, I just can’t get it to work. Script below

Any ideas

Allow user abort [Off]

Set Error capture [On]

Set Variable [$id; Value:PM_Contracts::PM_ID]

Go to Layout [“Equipment_Storage” (EQ_Store)]

New Record Request

Import Records [No dialog; Source: “equipment.xlsx”; Worksheet: “Sheet1”;Add; Windows ANSI]

Set Field [EQ_Store::PM_ID;$id] “Repetitions = 1”

Go to layout [Original Layout]

Thank you :-)

[Fitch]

After the import step, I would check the last error to make sure that records actually did get imported -- or you might end up changing the ID on existing records!

I don't know what's bothering you about using a loop, you described the steps (set field, then go to next record). In this case, though, you ought to be able to use Replace (with calculated value: $id), so a loop isn't needed. Turn error capture off and/or turn on your debugger so you can see what's failing (if anything).

[WSaxton]

I have used the debugger and seen no error on the bottom left , all the records do import they just dont recive the copied $Id only the first one does , I like the idea of the calculated result thats simple enough but how do i tell the script to put it into each imported record an no other ?

[Vaughan]

I have used the debugger and seen no error on the bottom left , all the records do import they just dont recive the copied $Id only the first one does

That's because the script only has one Set Field and it works on the current record (the first in the imported set).

As Fitch suggested, either loop through the records and set the ID or use the replace command.

[WSaxton]

Thats where im confused, how to write a loop first and formost and second how would i write a loop to place the id into each imported record? I would apprecate any help i could get with my current script

[LaRetta]

Pseudo-script would look something like this:

Allow user abort [Off]

Set Error capture [On]

Set Variable [$id; Value:PM_Contracts::PM_ID]

Go to Layout [“Equipment_Storage” (EQ_Store)]

New Record Request

Import Records [No dialog; Source: “equipment.xlsx”; Worksheet: “Sheet1”;Add; Windows ANSI]

If [ Get ( LastError ) // something went wrong or no imported records ]

Show Custom Dialog [ OK ; "No records imported" ]

Go To Layout [ original layout ]

Exit Script

End If

Loop

Set Field [ Equipment_Storage::ContactsPM_ID ; $id ]

Go To Record/Request/Page [ next ; exit after last ]

End Loop

Go To Layout [ original layout ]

or replace the portion in blue with: Replace Field Contents [ by calculation ; Equipment_Storage::ContactsPM_ID ; $id ]

I crossed out New Record Request because you are importing records so you don't need to create a new record in your Equipment Storage table (if I understand your needs correctly). I hope this explains a basic loop and RFC both. :^)

BTW, you normally want to begin with Go To Record/Request/Page [ first ] right before looping the records but I instinctively omitted it because after an import or a find, you are automatically on the first record. But you are not automatically on the first record with constrains or extends.

So when you write a loop, ask yourself what record will you be on when the loop starts and how do you plan to exit the loop. Both very important particularly the exit loop part, LOL.

Edited May 4, 2012 by LaRetta

[WSaxton]

it worked it worked holy crap it worked with no issues , you are the soooooooooooooo awsome LaRetta , but how did the script know to only go to the imported records and add the $id and not all the records in the file. I was kinda scared that it would change them all, even useing the script debugger which i know i control but i thought for sure it was gonna go back to the beginning and start changeing those but nope worked like a charm. Now why would i want to add an exit loop step to a looping script?

[LaRetta]

Now why would i want to add an exit loop step to a looping script?

Because if you do not, your file will stay in a suspended state of looping ... forever ...or until you throw a book at it.

The script knew to only change the ID on the imported records (and not all records in your table) because after an import, you only have the found set of imported records. Scripts (designed to run through records) only run against the current record set.

Looping is cool, huh? :laugh2:

Edited May 4, 2012 by LaRetta

[Fitch]

"after an import, you only have the found set of imported records" -- WSaxton please read my first post again and think about what might happen if the import fails. Better yet, TEST it!

[LaRetta]

Hi Tom,

I included a test for failure, didn't I? :^)

If I have something wrong in my script, please point it out.

Edited May 4, 2012 by LaRetta

[Fitch]

Your script is just fine*, but I wanted to reinforce your point to WSaxton about how imports work.

(*One thing I might do differently is go to the original layout before displaying the error message. And I suppose if we were being ultra-persnickety we'd check for error after each Set Field -- but it's unlikely that a just-imported record would become locked before the script had a chance to complete, unless we're talking about a very high-traffic system.)

[LaRetta]

I'm ultra-persnickety as well and going back to the main layout before displaying the custom dialog (and the underlying layout) is good point. I usually do; just didn't this time.

Since these are child records which will be inheriting the parent ID, even *I* would skip additional tests here. But that's why I use loop instead of RFC when served - ability to test within the loop when needed whereas Replace Field Contents will not tell you if a record fails until it has finished (at which point it is too late and you won't know which record failed).

Thanks for adding it ... it was important to mention. :^)