13 Replies Latest reply on Mar 5, 2013 9:41 AM by MikeF_1

    Clone from Actuals into Offers

    MikeF_1

      Title

      Clone from Actuals into Offers

      Post

           Phil,

           Uncertain if you’ll remember this scenario.

           See jpg attached - that script has been working fine for quite a while.

           There is another set of tables with the same structure – the Actuals table.  Where the word Offer would be the same as budget, Actuals is what happened in reality.

           The new objective is to adjust the script so that an Actuals event, along with its BoxOffice and Expenses records, can be cloned into the Offer schema.

           So as opposed to cloning a single record from Offer_Events, then multiple records from Offer_BoxOffice and Offer_Expenses into new records in their own respective tables [which the attached script does perfectly] ---

           The new Script would clone FROM Actual_Events, Actual_BoxOffice, and Actual_Expenses INTO the respective tables in Offer_Events, Offer_BoxOffice, and Offer_Expenses.

           Thereby making an event budget from a known [actual!] set of figures.

           Each of the relevant tables have the same field structure.

           Have made numerous attempts with some progress, but am certainly stalled.

           Will likely need to provide some more info, but checking initially to see if you think this can be accomplished.

           Any assistance will be greatly appreciated.

           Thanks,

            - Mike

      Script-AddNew_EventClone.jpg

        • 1. Re: Clone from Actuals into Offers
          philmodjunk

               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.

          • 2. Re: Clone from Actuals into Offers
            MikeF_1

                  

                 Phil,

                 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.

                  

                 "Actual" schema:

                 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..?

                 Thanks,

                  - Mike

                  

                  

            • 3. Re: Clone from Actuals into Offers
              philmodjunk

                   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.

              • 4. Re: Clone from Actuals into Offers
                MikeF_1

                     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.

                     Thanks Phil.

                      - Mike

                • 5. Re: Clone from Actuals into Offers
                  MikeF_1

                       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.

                       Thanks Phil.

                        - Mike

                        

                  • 6. Re: Clone from Actuals into Offers
                    philmodjunk

                         *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.

                    • 7. Re: Clone from Actuals into Offers
                      MikeF_1

                           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.

                           Thanks,

                            - Mike

                            

                      • 8. Re: Clone from Actuals into Offers
                        philmodjunk
                             

                                  $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.

                        • 9. Re: Clone from Actuals into Offers
                          MikeF_1

                                

                               Phil,

                               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.

                                - Mike

                                

                                

                          • 10. Re: Clone from Actuals into Offers
                            philmodjunk

                                 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.

                            • 11. Re: Clone from Actuals into Offers
                              MikeF_1

                                   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?

                                   Thanks,

                                    - Mike

                                    

                              • 12. Re: Clone from Actuals into Offers
                                philmodjunk

                                     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.

                                • 13. Re: Clone from Actuals into Offers
                                  MikeF_1

                                        

                                       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.

                                        - Mike