Selecting a field to go to a related record???

[FruitFul]

Greetings!

Brief/Short Version: When creating a new Record for Table A, can you go to a related record from Table A via a script?

There is also Table B, which might have several records in A linked to a single record in B (via a letter, in a portal). The field I want to go to a related record from, contains a letter (A, B, C, etc) that specifies which record within that record of Table B coresponds to it.

Example:

I am in Table B. I open a Layout which makes a new record within Table A but keeps it linked to that record in B. There is also a field which has a pop-up containing all records that exist in Table A for that record in Table B. Is it possible, within a script, to jump to the cooresponding Table A record and modify a field?

Longer/Detailed Version:

I am working with a Solution for a Photography Studio. All clients who have a photo session at the studio, also have a Review at a future date to go over the pictures taken.

Each session is its own entity/record (be it Review or otherwise), and are viewed in a portal in the clients account area.

On the 'Main' Page of the solution we also have a portal showing all 'Real' (non-review) sessions to track their status in-house. Pictures taken, edited, etc. The 'Due Date' is at whatever point the client schedules their review date for. (After their review we 'hide' the record so that it no longer appears in the portal)

Now here is the problem! When we make a new appointment for the clients review, I want for that date to be plugged into the record of the session it is for..

Example:

John Doe has just had his 3rd session with us. Within his account you can see his past experiences with us:

Sesion Type / Date

A / 12-17-03

REV / 1-04-04

B / 3-10-04

REV / 4-1-04

C / 5-5-05

REV / 6-17-05

Now, within the record for C there is a field for the 'REV' Date. This is the date we want to track on the home page for session status (and sort by it).

We want this to happen via a script, when the appointment for the review is set up. We want to try to stay away from making this a manual entry thing if we can help it.

The script already exists (assigns it a letter if it is a session, puts it into the calendar, etc), I am just hoping to modify it somehow to add this Review date to the coresponding Session (although the session and the review are currently NOT linked in any manor (except for being linked to a particular client within the Account Table), simply different 'types' of appointment).

One thing I tried was to have a pop-up of all their session letters (via a Value List). And then try to go to the related record for the one selected (then adding the Review Date which is stored in a global). But I cant seem to go to the related record (just stays in the current/new record that it is creating for the Review). Have tried using the 'Select field' option, etc... I think if I can go to the related record somehow I can make this work???

Any help or other ideas? Sorry if I am not descriptive enough { Let me know if there are any further details needed... Developed on a PC using Developer 7 and XP - Solution runs on normal FMP 7 on a few Macs.

~Joshua

[Slobey]

"Each session is its own entity/record (be it Review or otherwise), and are viewed in a portal in the clients account area."

How are these related to each other?

[FruitFul]

Each Account has a unique ID number. When you go to make a new session, you do it via one of these accounts and it carries that Accounts ID over to a field in that sessions record.

[Slobey]

I'm sorry, I may be confused. The unique ID number is for the client, correct? And then when you create a new session that ID is carried over and is related to the client. But if you have the senario you described of.

Sesion Type / Date

A / 12-17-03

REV / 1-04-04

B / 3-10-04

REV / 4-1-04

C / 5-5-05

REV / 6-17-05

Then all of the sessions have the same ID related to the client. But not a unique ID to relate Rev to A B or C. Am I not understanding you?

[FruitFul]

I'm sorry, I may be confused. The unique ID number is for the client, correct?

-Correct. Account Table has a UID.

And then when you create a new session that ID is carried over and is related to the client.

-Correct. Session Table has a field that is filled with the UID from accounts.

But if you have the senario you described of.

Sesion Type / Date

A / 12-17-03

REV / 1-04-04

B / 3-10-04

REV / 4-1-04

C / 5-5-05

REV / 6-17-05

Then all of the sessions have the same ID related to the client.

-Yes. All of these particular records would have the same UID in the Account UID field.

But not a unique ID to relate Rev to A B or C.

-Correct. As of right now, they are not linked to each other, except that they belong to the same account. Although you do have me thinking now I never thought of a self-join, but would I be able to make that work somehow? Need to rest and focus - cant wrap my head around this at the moment.

[FruitFul]

I found a solution that works Still open to ideas if there is a better method out there...

Currently added the following to the script:

If [ Sessions::Type = "Review" ]

Set Field [ Sessions::ReviewDate_G; Sessions::Date ]

Set Field [ Company::ReviewSession_G; Sessions::ReviewSessions ]

Go to Layout [ "Sessions" (Sessions) ]

Enter Find Mode [ ]

Set Field [ Sessions::CompanyID; Company::ID_G ]

Set Field [ Sessions::Session; Company::ReviewSession_G ]

Perform Find [ ]

Enter Browse Mode

Set Field [ Sessions::MRevDate; Sessions::ReviewDate_G ]

End If