8 Replies Latest reply on Sep 2, 2010 10:30 AM by philmodjunk

    Compare data before and after records are edited

    RedL

      Title

      Compare data before and after records are edited

      Post

      I work on a Sales Order project.  I have SalesOrder and SOLine tables.  SOLine is the detail products line for SalesOrder and it is a portal inside SalesOrder Layout.  It works now.  And I need to compare the portal each lines record fields between "before enter to a SalesOrder" and "after Exit that SalesOrder".  I need to record the changes during SOLine editing.  How can I do it?

      1.jpg

        • 1. Re: Compare data before and after records are edited
          philmodjunk

          You can use an onObjectEnter triggered script set on the Portal to capture all the field values in global variables. Then you can set an OnObjectExit trigger on the portal to run a script the compares the values of the current portal row with the values in the variables.

          • 2. Re: Compare data before and after records are edited
            RedL
            Yes, it is what I do now.  But, it will be a lot of information I need to capture.  If this is the only one way, I have no choice.
            • 3. Re: Compare data before and after records are edited
              philmodjunk

              What problem are you trying to solve with this approach? Perhaps there's a completely different approach that will work better.

              • 4. Re: Compare data before and after records are edited
                RedL

                I have no problem with this approach.  I am thinking for any other solution for this kind of requirement.  Thank you for your comments.

                • 5. Re: Compare data before and after records are edited
                  philmodjunk

                  I believe there's a plug in that offers some form of data security that may help--you'd need to web search for that unless another forum members posts the info here.

                  You can also lock the records so that no further editing is permitted--That's what we do here once an invoice has been printed. A supplementary invoice has to be created to correct an error at that point and this keeps the original data entered intact as well as documenting the fact that an error occurred.

                  We also log key data from our invoices at key points in time during the data-entry process. If a discrepancy between the final copy and the first such logged "copy" doesn't match, the transaction is flagged for management to take a closer look.

                  • 6. Re: Compare data before and after records are edited
                    RedL

                    OK, I will try it.

                    I will "log key data" in the future also.  May you advise how you do it?  By the way, I will make a copy of one Sales Order, including SOLine to different tables.  Can you advise how to do it?

                    • 7. Re: Compare data before and after records are edited
                      philmodjunk

                      Our business proceedures are not typical--so you'll have to look at the techniques and decide if they can be adapted to yours.

                      We redeem used beverage containers and buy scrap metal--that means money flows the opposite direction from most POS environments.

                      One user, the weighmaster, weighs and identifies the materials and identifies the customer--filling out an invoice not too different from what you've posted. When he's finished and confirmed ths with the customer, he clicks a button that marks the tag as "ready for cashier". This script also creates a record in a hidden security log that records the record's serial number, the name of the weighmaster (this is also auto-entered into the invoice), the total of the tag and certain selected material subtotals common to most tags.

                      Another user, the cashier has a contiually updated screen listing all invoices flagged as "ready for cashier". They click a Print button to print the invoice. That print button also makes a new record in the log recording the cashier's name as well as the same numbers I've already described. It also changes the invoice's status to "printed" which sets lock condition to evaluate to true--locking the invoice and lineitems tables against any further editing. Once printed, an employee can void the tag and make a new one, but they can no longer modify it as this creates a discrepancy between the hard copy and the electronic.

                      In the log, any invoice that has more than two records with that serial number or which have total values that don't match between the two logged records are flagged for closer scrutiny.

                      • 8. Re: Compare data before and after records are edited
                        philmodjunk

                        I will make a copy of one Sales Order, including SOLine to different tables.  Can you advise how to do it?

                        We make a invoice by invoice back up of our invoices by performing a script in a second file that imports the invoice and related line item records each time we "print" an invoice.

                        This takes three scripts.

                        Script 1 isolates the current invoice in a found set of one record and then uses Perform Script to call a script in the back up file to import the Invoice record.

                        It then uses Go To Related Records to pull up the Line Items in a found set and calls a second script in the back up file to import the line items.

                        I use tables in the Back up file that are exactly the same as the tables in the Main file so that I can import with the matching names option to keep the import as safe and trouble free as possible.

                        Oh yes, we also have Filemaker Server make hourly backups of the main file as an additional level of protection.