Can you explain why you need to move the data into separate tables instead of keeping the data in the original tables? This is something that is often not necessary.
In the "Offer" [or budgets schema]:
Table ... Offer_Events contains budgeted info for the current event, related to BoxOffice and Expenses by Offer_EventID.
... Offer_BoxOffice contains budgeted box office info for the current event related by Offer_EventID.
... Offer_Expenses contains budgeted expense info for the current event related by Offer_EventID.
Table ...Actual_Events contains actual info for an event that has occured in the current venue related to below by Actual_EventID.
... Actual_BoxOffice contains actual box office info for an event that has occured in the current related by Actual_EventID.
... Actual_Expenses most importantly contains actual expenses that have occured in the current venue related by Actual_EventID.
An event occurs in a venue. The more events you do in a venue, the more history you'll have to base expenses on.
So if you have to do a budget for a particular venue, it's much better to use actual historical info than previously budgeted info.
Scenario would be that when you need to budget for a particular venue, you filter a layout for previous events in that venue. When you see an event that would be a good match for your budget, you then "clone" it into the Offer schema.
The attached screenshot's script works fine, except it is cloning Offers into Offers, that were originally estimates in the first place.
Objective would be to adjust the script so it can clone Actuals into Offers, thereby making the budget for that event much more accurate.
Does this make sense now..?
Ok, take a look at a completely different method for moving the data then.
Import records can be used to copy a found set of records from Table A to Table B.
And this can be scripted.
Well, I got started and didn't get much past SetVariable: Actual_Events::$EventD.
Went to import records, but it seems to want a file.
Any assistance will be sincerely appreciated.
Testing the script on just the Actual_Events to Offer_Events tables first.
Got this far ...
[When on the desired record in Actual_Events]
Set Variable = $EventID
Set Variable = "file:" & Get (FileName) & ".fp7"
Go To Layout = Actual_Events
Perform Find = $EventID
Import Records = NoDialog; $Filename; Add; WindowsANSI
This works, however the Actual record's EventID, say 20 in the Actual table, should turn into say 615 in the Offers table. The EventID stays at 20, *even though* that field is set to Serial Number, Unique *and* the Auto-Import settings are engaged. And I can import lots of these records, the EventID stays at 20.
So put a Set Field into the script of Max (Offer_EventID) +1. But that only makes the EventID 21 ...!
I don't get this at all.
Any help will be greatly appreciated.
*even though* that field is set to Serial Number, Unique *and* the Auto-Import settings are engaged.
Better check those settings--bot in the script and in the serial number field. If the auto-import check box is selected, new auto-entered serial numbers will be auto-entered. But also keep in mind that all auto-enter settings on all fields in your record will also be enabled--sometimes this solves one problem and creates a new one.
As an alternative, import records produces a found set of imported records. You can use Replace Field contents on this newly created found set to modify fields--even assign serial number values, all in one batch operation if that is necessary.
To deal with the serial number issue, have set the following variable, which is working:
$MaxOfferEventID = Max (Offer_EVENTS::EventID) + 1
Don't know why this needs to be done, but am over it since the above workaround does that task.
Looked at the Replace Field Contents already this morning from another script, and believe I can get that working.
Will let you know.
$MaxOfferEventID = Max (Offer_EVENTS::EventID) + 1
That's not an auto-enter setting on a field so the check box I mentioned has no effect on it.
Please see attached screenshot.
Note - "Sett" is the same thing as "Actual" re previous references.
Everything works down to thru line 11.
When it gets to line 12, Import Records, the error says "No fields were selected for import".
Thanks for your assistance.
Using a variable for the file reference is tricky. I know of a way to handle that issue, but you don't, in this case, actually need to use the variable.
On the Import Records step, select "file" from the drop down to the right of the Specify Data Source check box. Then click the Add File button. Use the dialog box that opens to select the very file in which you are creating this script. Then click the Specify Import Order button and select the options you need for importing your records.
This will work just fine as long as you do not change your file's name.
If you want to stick with using the variable so that a change in the file's name does not require an update to this script, select "File" again and type in the path variable's name so that it is the first line of text in the Specify File dialog, but keep the text produced when you clicked Add File as the second line of text. When editing the script to specify the import options, FileMaker will use the second line to reference the file, but when the script is performed, the first line will be used so long as the value put in the variable is a valid file reference.
Set Variable $OfferEventIDMax = Max (Offer_EVENTS::EventID) + 1 *was* working fine, but it has gone south.
The max EventID in Offers_EventID is 602, so this should be returning 603.
Starting in a layout based on Sett_Events with an EventID of 26, this is returning 27. The filtered events in this layout end in 27. There has to a connection.
Will send you a screenshot if necessary, but any ideas why this is occuring?
I really don't see why you would need this in the first place and such a method in a multi-user database can result in duplicate eventID values.
I need a lot more detail in how you are using this expression and why an auto-entered serial number field can't do the job for your.
I don't know why the auto-enter doesn't work either. At least when importing from Sett_Events.
The auto-enter does work when using Duplicate Record in Offer_Events.
Have found a solution that works, but it is still using the convoluted Max EventID variable.
Will send you a screenshot once everything comes together a bit more.
Thanks as always.