You'll need to be careful on the order of data entry.
You create record 101.
It has a field called Next that should hold the value 103 or whatever number is to be next.
But it can't hold the value 103 since 103 doesn't exist yet. That record has not yet been created.
So you'll need to create all of your records and then go back over them and enter the Next value.
To fill in the values for Next after all records are created, you could sort the records by Code and do something like this calculation:
GetNthRecord(MyTable::Code; Get(RecordNumber) + 1)
This could be applied by a Replace Field Contents... with Calculation.
An alternate method that builds in the feature to calculate on-the-fly and does not need you to do the data entry ahead of time is that
you could set up a relationship between the two
MyTable::Code <------=-------> MyTable__Next::Code
where = is defined with Code < Code and the records for MyTable__Next are sorted ascending by Code
Then a calculated field defined as
will be the value from the next higher record.
If you are so inclined you could also do this calculation using ExecuteSQL().
Hello, Tom --
Allow me to elaborate: all of the records have been created. We know what every seminar is and all of their basic information exists. So yes, the record for Seminar 103 exists, the Next field for Seminar 101 contains the value 103, and the calculation field entitled NextDisplay has been created, as well.
Now I need to fashion some sort of calculation that knows how to pull the values from the record identified in the Next field and make them part of a calculation in the NextDisplay field. Any ideas?
If I understand the situation now, you already have the Next field populated with the correct values. Now you want to display additional fields from the record referenced by Next.
That is done with a Relationship.
In the Manage Database-->Relationships you need to set up another Table Occurrence for your table. I'll call the new Table Occurrence "MyTable__Next".
MyTable::Next <-- = --> MyTable__Next::Code
where the = is equality between the two fields.
Now you can put onto your layout or use in calculations Related fields that start with MyTable__Next:: such as
Thank you very much. This is a bit ambitious for me (I'm much better with calculations and scripts than I am with relationships), but I will give it a go...
How is your database structured? If you have your venues in their own table (and it seems to me you should) with join tables to link them to the events that will run in them, then it is a simple matter of generating a venue report in order to list the events that will take place in any given room.