Setting a field from another, unrelated table

[merlyn383]

Ok, first I'm going to simplify the problem down as much as I can:

I have two tables: "Course Admin" contatins grade and a text field for the grading scheme. There are several records for several different courses in this table. "Calc" is a customized virtual calculator that I created to facilitate the creation of the grading scheme. There s one record, with one field called calculation which is set global. There is no relation between the tables.

I have script set up that when executed:

1. passes the grade scheme of the current record to the calculator (uses "set field")

2. opens the calculator file

3. switches to the proper layout for the course

Now, once the user is done changing the calculation, I want them to hit a button and have the new calculation passed back to the original record's "grade scheme" field. How can I specify pricisely which record in which table to pass the new value.

In the implemented database I actually have 7 tables and several courses. Thus, creating a calculator for each course and keeping everything inside of each courses' table would be hundreds of scripts, which I really want to avoid.

[mr_vodka]

you can have a global record that sets the last record number and go back to that record, however, I'm not sure that the way you have it set up currently would be the most proper way to structure your database. Can you attach a copy of the file?

[merlyn383]

Aren't record numbers relative to the current found set, thus an unreliable way to navigate?

I could be wrong, but I thought this was how it worked.

[mr_vodka]

Sorry. I meant the Unique record ID number rather than the record number of the found set.

[merlyn383]

I tried using the record ID, and calling the GRRP script and passing it the record ID. It works sometimes, but the GRRP will go use it's parameter and go to record relating to either the record number or the record ID, so there is no garauntee that it will go to the correct record. Is there a better script step to use instead of GRRP?

[Lee Smith]

What is GRRP? Is this something new in v7, or do you mean GTRR as in Go To Related Record?

[merlyn383]

Go to Record/Request/Page

I don't know if it was in 6 or not, I went from 5.5 to 7.

From the FMP 7 help file:

Go to Record/Request/Page script step

Format:

Go to Record/Request/Page [<first/last/previous/next/by calculation>]

Options:

Specify lets you choose from the following options.

First moves to the first record in the file or found set, displays the first find request, or moves to the first page in a report.

Last moves to the last record in the file or found set, displays the last find request, or moves to the last page in a report.

Previous moves to the previous record in the file or found set, displays the previous find request, or moves to the previous page in a report. Exit after last tells FileMaker Pro to stop the script when it reaches the last record in the found set. You can use Exit after last with the Loop script step to exit out of a loop after the last record.

Next moves to the next record in the file or found set, displays the next find request, or moves to the next page in a report. Exit after last tells FileMaker Pro to exit the script or control structure (like a loop) when it reaches the last record in the found set.

By Calculation lets you create a calculation to determine the index number for the desired record, find request, or page in Preview mode.

Note

[-Queue-]

GRRP uses record number, i.e. it is dependent on found set and sort. Go to Related Record will do what you want. You can set a global with the original record's serial field, create a relationship between the global and the serial, then GTRR [show only related; "yourRelationship"] when you are back on that layout and want to return to the record.

Of course, this is all superfluous if you do not navigate to a different record on that layout. When you return to the layout, you should already be on the most recently selected record.

[merlyn383]

I know that it may be superfluous, but I can't guarantee that my users won't do something silly like that accidentally (or on purpose). Thanx for the help -Queue-.

[Søren Dyhr]

Now, once the user is done changing the calculation, I want them to hit a button and have the new calculation passed back to the original record's "grade scheme" field. How can I specify pricisely which record in which table to pass the new value.

In the implemented database I actually have 7 tables and several courses. Thus, creating a calculator for each course and keeping everything inside of each courses' table would be hundreds of scripts, which I really want to avoid.

This looks almost like a price matrix, which usually are done by putting the Lookup function as well as the LookupNext inside a single Set Field[ scriptstep ...Take a look at this thread:

http://www.fmforums.com/threads/showflat...true#Post142375

--sd

[merlyn383]

OK, I tried using the GTRR idea, but I'm getting some errors that I can't figure out.

In my Course Admin table I defined a field "RID" as a calculation and set it to Get ( RecordID ). Then in Calc, I defined a text field RID and set it as a global.

In the script that calls the calulator, there is a "SetField ( Calc::RID ; Course Admin::RID )".

The calculator has an equal relationship between Calc::RID & Course Admin::RID. However, when I try to use GTRR, it tells me "This operation can not be performed because one or more of the relationships between these tables are invalid." Obviously, I get the same error when I try to set any field in Course Admin.

I tried playing around with the options for GTRR, no help. The settings that make the most sense to me are:

GRR from: Course Admin

Use external table's layouts (since the fields that I want to set are not in the current file)

Show record using layout: Course Admin Default

Show Only related records.

Is my logic right in my choices? Like I said, I tried various other settings to no avail, but I would like to know if my train of thought was on the right path. Any ideas why FM is saying that the relationship is invalid?

[-Queue-]

Is Course Admin::RID unstored? You'll need to store it to use it a child key for the relationship.

Ah, also Set Field [Calc::RID; Course Admin::RID] will not work because the relationship is not yet valid when you call it. You do not want to set key fields in such a manner anyway. Instead, set a global in Course Admin with the id and base your relationship on that table only. When you return to that layout, use GTRR and the self-relationship to return to the original record in the same layout.

[merlyn383]

Thanx -Queue-. My problem was that Course Admin::RID was unstored. Made the field stored, now everything works with the relationship I had defined and everything.

I have a question on the second part of your post though. The Set Field does work because Calc::RID is a global, so it can set the field without the relationship. My question is why is this method inadvisable?

[-Queue-]

My bad. I guess I didn't notice it was a global. Either that or having a table named Calc threw me.