5 Replies Latest reply on Jan 12, 2012 10:08 AM by philmodjunk

    Trigger auto-enter calculations AFTER importing is complete?

    GabeDiaz

      Title

      Trigger auto-enter calculations AFTER importing is complete?

      Post

      Hello All,

      So let's say I have two files: Database.fp7 and Mobile.fp7. Database runs on a MacMini and Mobile runs on an iPad.

      I have a calculation field set up in the Database which calculates the time elapsed between one record and the record before it. I would like this calculation to take place solely based on the Time value in my database. My mobile file also has this Time value. I have my import set up to run Auto-Calculations on import. But the calculation is being done based on the Time values in the Moble file and not in the Database file. Sometimes for whatever reason, my users will modify times in either the Database or the Mobile file (I keep the last 30 days of records in the mobile file).

      So if my users do an import and then change the time in the database, and then do another import, the Elapsed value ends up wrong. I mean... technically it is correct... but it is not calculating based on what is in the database. See the 4th set of values in my attached screenshot. In this example, the 04:00 record's Time was changed to 4:02 on the iPad but left alone in the database.

      Is there a way I can manually trigger the auto-calculation after the import has completed?

      Thank you.

      Screen_shot_2012-01-08_at_5.16.20_AM.png

        • 1. Re: Trigger auto-enter calculations AFTER importing is complete?
          philmodjunk

          Auto-enter on import is an all or nothing option. Either all auto-enter field options are triggered on import or none of them.

          In your situation, you can use Replace Field Contents to perform the same computation on this field immediately after the import. THis can be done manually or as part of your import script. (Replace Field Contents changes all the records in your found set. Immediately after import, your found set will be all the newly imported/updated records.)

          • 2. Re: Trigger auto-enter calculations AFTER importing is complete?
            GabeDiaz

            For some reason, when I use Replace Field Contents, I get a ? for the result of my calculation. My elapsed calculation is as follows:

            Let ([ Record_Number = Get ( RecordNumber ) - 1]; ((Database::Date - GetNthRecord ( Database::Date ; Record_Number)) * 86400) + Database::Time - GetNthRecord ( Database::Time ; Record_Number) )

            Some things I have tried to remedy:

            Added "Database::"
            Tried instead of Replace with "current contents" Replace with calculation "Time + 1"
            Tried placing the Replace with "current contents" function inside of a second script and had the first script run that at the end.
            Tried putting more functions in between the Import and Replace functions.

            However, one thing that does work is if I Show All Records before running the Replace function but then this replaces the Time of every single record in my databse so I can't have that... but it does trigger the calculaton... which basically makes all the Elapsed times 00:00. Kind of odd. Hopefully you have a clue.

            Thanks for your help. 

            • 3. Re: Trigger auto-enter calculations AFTER importing is complete?
              philmodjunk

              The problem is that you getnthRecord calcualtion fails for the first record and then you get ? for it and all subsequent records.

              GetNthRecord refers to the record immediately before it in the current found set as controlled by any sort order specified for it. (No sort order, they'll be in order created. or imported in this case.)

              Are all imported records to appear after all previous records?

              If so, you can go to the oldest existing record to capture the values needed from those fields in variables before you perform the Import records step and then include them in your replace fields calculation. (If it's not all one script, use global variables.)

              Let ([ Record_Number = Get ( RecordNumber ) - 1;
                       Prior_date = If ( Record_Number ; GetNthRecord ( Database::Date ; Record_Number) ; $PriorDate ) ;
                       Prior_time = If ( Record_Number ; GetNthRecord ( Database::Time ; Record_Number) ; $PriorTime )
                     ];
                       (Database::Date - Prior_date ) * 86400 + Database::Time - Prior_time
                    )

              • 4. Re: Trigger auto-enter calculations AFTER importing is complete?
                GabeDiaz

                Now my calculation came out really weird. I'll try looking more into it later my brain hurts now. Unless maybe you know off the top of your head what is wrong.

                • 5. Re: Trigger auto-enter calculations AFTER importing is complete?
                  philmodjunk

                  How did your script capture the values for $PriorDate and $PriorTime?