Use records from 1 table ---> update records in another

[skrying]

So I have the following tables & fields:

user::memberID

roles::memberID, roles::year, roles::status

currentRoles::memberID, currentRoles::year, currentRoles::isParticipant, currentRoles::isStaff...

and the following relationships

roles::memberID = currentRoles::memberID

roles::year = currentRoles::year

So each user has a roles table for various years. Roles::status contains text, such as "Participant," or something else.

What I want to do is move all the roles information into the currentRoles table. Currently, a user will have multiple roles tables if they have multiple status' for the same year. So if a user has two roles tables with year=2006 for both, but status="Participant" and status="Staff," I want to make one currentRoles table with isParticipant="yes" and isStaff="yes."

I was able to create a script that made all the correct instances of the currentRoles table, but I am having trouble populating them with the right data.

Go to Layout ["roles", (roles)]

Show All Records

Go to Record/Request/Page [First]

Loop

  If [isEmpty (currentRoles::memberID) and isEmpty (currentRoles::year)]

    Set Field [currentRoles::memberID; roles::memberID]

    Set Field [currentRoles::year; roles::year]

  End If

  # not working vvv

  If [Exact (roles::Status ; "Participant")]

    Set Field [currentRoles::isParticipant; "yes"]

  End If

  If [Exact (roles::Status ; "Staff")]

    Set Field [currentRoles::isStaff; "yes"]

  End If

  # not working ^^^

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

End Loop

The above script creates the correct number of tables, with the correct memberIDs and years, but it doesn't mark isParticipant and isStaff the right number of times.

Any ideas?

Edited November 5, 2005 by Guest
typos

[-Queue-]

It may just be typographical error, but both of your Ifs appear to be incorrect.

If [Exact[ roles::Status ; "Participant" )]

should be

If [Exact( roles::Status; "Participant" )]

and

If [Exact roles::Status; "Staff" )]

should be

If [Exact( roles::Status; "Staff" )]

[skrying]

Yeah, those were just typos.