Title
irregular sequence
Post
I need to build a table of publishing dates. the product publishes on the first and fifteenth of each month but skips Jan 1 and July 15..
From that table I need to generate:
volume and issue numbers (new vol. every year, unique issue numbers: 1 ... xxx
issue dates
end dates for subscriptions (start plus 21 issues),
current issue number and date (to generate text and stories based on release dates)
etc.
do I need to hand build the basic table every year or so or can I set up a formula to do this based on the previous record(s)? would it involve 24 serial if statements? (I just moved up from FM6 to 11 pro advanced so I'm not familiar with all the new bells adn whistles).
Any help appreciated.
You can define a table of Volumes--one record for each volume and then an auto-entered serial number can assign the next volume number each time you create a new record.
You can define a table of issues--one record for each issue linked to the volumes table by the Volume number.
A script can generate a years worth of records (22) in the issues table. Either the script can use a counter to number each new record consecutively or the script can reset a field's next serial value setting and then each new issues record will automatically number itself in sequence starting with 1 as it is created.
Go To Layout [Volumes]
Show All Records
Sort [Restore ; no dialog ] // not absolutely needed, but makes sure to sort records in ascending order by volume number.
Go To Record/Request/Page [Last]
new Record/Request
Set Field [Volumes::Year ; GetNthRecord [Volumes::Year ; Get ( RecordNumber ) - 1 ) + 1 ]
Set Variable [$Year ; Volumes::Year ]
Set Variable [$Vol ; Value: Volumes::VolumeNumber ]
Go To Layout [Issues]
Set Next Serial value [Issues::IssueNumber ; 1 ]
Loop
Set Variable [$I ; Value: $I + 1]
Exit loop If [$I > 24 ]
If [ ( $I > 1 ) and ( $I ≠ 14 ) // 1 and 14 are Jan 1 and Jul 15 ]
New Record/Request
Set Field [Issues::VolNumber ; $Vol ]
Set Field [Issues::IssueDate ; Date ( ceiling ( $I / 2 ) ; If ( Mod ( $I ; 2 ) ; 15 ; 1 ) ; $Year )
End IF
End Loop
Notes:
The If block skips creating a record on Jan 1 and Jul 15.
Ceiling ( $I/2 ) computes the month number.
IF ( Mod ( $I ; 2 ) ; 15 ; 1 ) computes a day number of 1 for odd numbered values of $I and 15 for even numbered values.
$Year is computed from the most recently created Volume record to be that year + 1. The first record in this table would need to be created manually.
The volume records are sorted into the same order they would normally be found if they were unsorted, but if the records in this table are ever imported from another copy of this database, (Say after recovering a damaged copy or when replacing the current copy with an upgraded copy), it's possible for them to be imported in a different order and then the unsorted order might not be correct--so the sort is to make sure of the correct order.