GTRR trouble

[The Mad Jammer]

I have a table named Items and two fields in the table Stock_Number and glb_Stock_Number. I have defined a relationship between the two in the database as well (rel_Stock_Num).

I fill in glb_Stock_Number with return delimited values, along with some other global fields that will be used to populate associated fields on the Items table. Once all the global fields are filled in with appropriate values I run a script to loop through the glb_Stock_Number values and update the associated records. here's the script

Go to Related Record [show only related records; From table: "rel_Stock_Num"]

Loop

Set Field[rel_Stock_Num::field1; Items::glb_field1]

Set Field[rel_Stock_Num::field2; Items::glb_field2]

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

End Loop

The problem is that only the first record is being updated and the other values in glb_Stock_Num are being ignored.

-QUEUE- helped me get this script working in version 5 but it isn't working in 7.

Why is this not working correctly?

Thanks

The Mad Jammer

[-Queue-]

I do not recall how this was set up. Can you post a sample of the file?

[SlimJim]

I may be misunderstanding the question, and its late (for me) so apologies if this is rubbish. It seems to me that you wish to move through records in the Items table whose stock number is one of the numbers in the glb_Stock_number fields, whereas your script appears be going in the other direction (through the rel_Stock_Number records and hence will update only the record you started with.

[The Mad Jammer]

- QUEUE -

You sent me a piece of script in the Batch Update thread in The Left Brain forum 11/19/04. It worked wonderfully then under version 5.

SlimJim,

You are correct in your assertion that I want to goto the records in Items that have the same stock numbers as the ones in glb_Stock_Number and update the Items record. Perhaps I am going in the wrong direction. I'll turn these around and try it again.

One other question. It used to be that the fields to be updated had to be in the current layout. I have a separate layout to collect the data that I want to update the Items records with. I suspect that the GTRR script step will take care of making the record current and exposing those fields I want to update without having to specify "Go To Layout[items]". Tell me if that's wrong.

Thanks

The Mad Jammer

[SlimJim]

Set Field does not require the field to be in the current layout only in the current record. The GTRR step includes specifying a layout in which to see the related records so there is no need for a separate Go To Layout step.

[The Mad Jammer]

Well we're getting closer. Now the script updates a bunch more of the reocrds than are defined in the global stock number list. My script looks like this.

Go To Related Records(Show only related records; From table "Items for Batch Update"]

Loop

Set Field[items::Field1; Items::glb_Field1]

Set Field[items::Field2; Items::glb_Field2]

Set Field[items::Field3; Items::glb_Field3]

Set Field[items::Field4; Items::glb_Field4]

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

End Loop

"Items for Batch Update" is the name of the relationship (TO) I set up between the Items::glb_Stock_Number field and Items::Stock_Number.

I entered 5 unique stock numbers in the glb_Stock_Number field and updated 267 records.

I checked the relationship and its very straightforward, only glb_Stock_Number = Stock_Number and I don't have any duplicate stock numbers in this database. Obviously, I'm doing something wrong here, but what?

Thanks

The Mad Jammer

[-Queue-]

Which TO is associated with the layout you are on when the script is called? On which layout are you displaying the related records in your GTRR step?

[The Mad Jammer]

-Queue-

I have a separate layout named "Batch Update" that contains all of the global fields (there are 15 of them, represented in a preceeding post as glb_field1, glb_field2, etc...) that I am going to use to update the Items table. The glb_Stock_Number field is on this layout as well. These fields and the button that executes the update script are the only objects on the layout.

In the layout setup of the "Batch Update" layout - "Show records from Items" is selected, so that is the TO being used on this layout. Should I have selected "Items for Batch Update" as the TO in this layout setup?

The fields in the Items table that are going to be updated reside on another layout called "Items", not to be confused with the "Items" table. I do not go to this layout until after the batch update script executes.

Thanks

the Mad Jammer

[-Queue-]

How many records are in the found set before the loop executes?

You may need to attach the file for us to settle this cleanly.

[The Mad Jammer]

Every record in the database is the found set. The database is huge at this point. I'll have to do a small extract in order to send it up to the forum./

The Mad Jammer

[-Queue-]

A clone is fine. No records are necessary.

[The Mad Jammer]

Here's a cloned file to look at.

the Mad Jammer

ItemsFMP7.zip

[-Queue-]

Your Batch Update layout needs to be based on the Items for Batch Update TO and the GTRR should use the Items relationship. You cannot go the other way because the key field is a global, so there are no related records.

[The Mad Jammer]

Thnaks -Queue-,

It's finally working except for one small issue with a couple of the fields not being filled in. I'll have to look at the Set Field commands to be sure they are doing what they should be doing.

I had to use the "Show record using" Items int the GTRR options dialog box in order to update all of the records. If not only the first record in the list got updated. Not sure why that happened, but at least I'm getting the correct records to update.

Thanks again for your help.

Here's a nice frosty one for you.

The Mad Jammer