Script to find related field between two tables

[hoon]

Hello again,

I have 2 tables that share a field called dbnum for database number, which in table 1 is a unique number but for table 2 is a number with multiple records with different variations. The tables are a 2 step process to track if an action had been done or not. The first table is to prep the action with the unique number. I have another field called Status with the radio buttons "done", "not done", and "redo". I don't want to set the field with a calculation so that I can manually set the radio buttons if I want. When adding records to the first table, the default is to mark them "not done".

Step 2 is when I'm importing a report of the action being done into table 2. I want to run a script that will see if the dbnum in table 1 matches the dbnum in table 2, and if so then to mark the Status field as "done". There are multiple takes of the action so that the following happens.

[table1]100 - [table2]100 - take 1

[table1]100 - [table2]100 - take 2

[table1]101 - [table2]101 - take1

I've tried several variations of this script and it doesn't seem to work. I've made sure that the dbnums between the 2 tables are linked.

Go To Record/Request/Page [First]

Loop

Go To Field [Table1::Status]

If [Table1::dbnum = Table2::dbnum]

Set Field [Table1::Status, "Done"]

End If

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

End Loop

Thanks

Hoon

[mr_vodka]

If you are just checking to see if there is a related record is Table 2 for the records that are "Not Done", then you can do something like this...

Enter Find Mode []

Set Field [ Status; "Not Done" ]

Set Error Capture [ On ]

Perform Find []

If [ Get ( FoundCount ) ]

Replace Field Contents [ Status; Case ( not IsEmpty ( Table2::dbnum ); "Done"; Status ) ]

End If

[hoon]

Thanks for the suggestion but the find count will always be different between the 2 tables. The first table (the "pre" action) will have only unique dbnums per record, but second table (the "post" action) will have multiple records per dbnum.

I guess it is similar to filming a movie where you want to shoot a specific shot so in table 1 you have shot 1. When you film it you will have multiple takes of shot 1. So what I want this script to do is to make sure that once I've "filmed" shot 1, then it will mark in table 1 that it is done. I hope that makes sense.

[hoon]

I'm trying a different approach for this now since I couldn't get it working. Now what I'm trying to do is compare the number of records between the two tables to see if it is > 0.

So if my dbnums match, I've created a field that is a calculation to get the summary per dbnum on the post table. Therefore if the summary num is > 0 for each of the dbnum then in the pre table the done field should be marked.

Will this work?