13 Replies Latest reply on Jan 15, 2015 6:37 PM by philmodjunk

    Mysterious Record Updates

    IsaacKnoflicek

      Title

      Mysterious Record Updates

      Post

      I'm working with a modified version of the Invoices Starter Solution in FileMaker 13 and I've added an auto-enter modification timestamp field and an auto-enter modification account name field to the "Invoice Data" table. 

      For the most part these fields work as expected, however we've had 2 instances in the last month where somehow a user has touched all the records.  In both cases it looked like whatever they did just updated the timestamps, I don't see any other bulk changes.

      In both cases I had the users repeat what they were doing but I haven't been able to recreate the timestamp update.

      In one case it was a power user with limited layouts available who was just entering invoices.  In the more recent case it was an admin working in the data sheet view of the "Invoice Data" table.

      Obviously there's something unique happening and I'm wondering if you guys have any ideas on either buttons or keys they could accidently be hitting that could cause this, or if you have any ideas on how I could prevent or diagnose it in the future.

      Thanks,
      Isaac

        • 1. Re: Mysterious Record Updates
          SteveMartino

          I would check for layout/field triggers that contain 'Replace Field Contents'  Could be something that is touching every record.  I would also remove items via custom menus that could touch every record.

          • 2. Re: Mysterious Record Updates
            philmodjunk

            Searching a Database Design Report for "Replace Field" might be one way to thoroughly check for this. Producing a Database Design Report requires a copy of FileMaker Pro Advanced.

            • 3. Re: Mysterious Record Updates
              IsaacKnoflicek

              I've got FM Pro Advance.  There is one instance of "Replace Field" in that report but I'm not sure of the context.  Here's the chunk of text:

                          <Calculation><![CDATA["ELSE Record openstate neq 1 block"]]></Calculation>
                                      <DisplayCalculation>
                                          <Chunk type="NoRef">&quot;ELSE Record openstate neq 1 block&quot;</Chunk>
                                      </DisplayCalculation>
                                  </Title>
                                  <Buttons>
                                      <Button CommitState="True">
                                          <Calculation><![CDATA["OK"]]></Calculation>
                                          <DisplayCalculation>
                                              <Chunk type="NoRef">&quot;OK&quot;</Chunk>
                                          </DisplayCalculation>
                                      </Button>
                                      <Button CommitState="False">
                                          <Calculation><![CDATA["Cancel"]]></Calculation>
                                          <DisplayCalculation>
                                              <Chunk type="NoRef">&quot;Cancel&quot;</Chunk>
                                          </DisplayCalculation>
                                      </Button>
                                      <Button CommitState="False"/>
                                  </Buttons>
                              </Step>
                              <Step enable="True" id="70" name="End If">
                                  <StepText>End If</StepText>
                              </Step>
                          </StepList>
                      </Script>
                      <Script includeInMenu="False" runFullAccess="False" id="50" name="Trigger | Update Invoice Data">
                          <StepList>
                              <Step enable="True" id="91" name="Replace Field Contents">
                                  <StepText>Replace Field Contents [ Invoice Data::Date; Replace with calculation: Invoices::Date ]
              [ No dialog ]</StepText>
                                  <NoInteract state="True"/>
                                  <With value="Calculation"/>
                                  <Calculation><![CDATA[Invoices::Date]]></Calculation>
                                  <DisplayCalculation>
                                      <Chunk type="FieldRef">
                                          <Field table="Invoices" id="5" name="Date"/>
                                      </Chunk>
                                  </DisplayCalculation>
                                  <SerialNumbers UpdateEntryOptions="False" UseEntryOptions="True"/>
                                  <Field table="Invoice Data" id="39" name="Date"/>
                              </Step>
                          </StepList>
                      </Script>
                      <Script includeInMenu="False" runFullAccess="False" id="16" name="-">
                          <StepList/>
                      </Script>
                      <Script includeInMenu="False" runFullAccess="False" id="61" name="Trigger | QuickFind Customers">
                          <StepList>

               

              • 4. Re: Mysterious Record Updates
                philmodjunk

                I suggest generating an HTML version of your report so that you can post a plain text version of your script by copy/pasting from there.

                • 5. Re: Mysterious Record Updates
                  philmodjunk

                  There are three ways that I can think of that would cause an update of a ModifyDate field on all records in your table:

                  Replace Field Contents after a show all records

                  Importing all your data into a clone of the file with "enable auto-enter options" specified

                  A looping script that loops through all the records and sets a value in a field on every record

                  • 6. Re: Mysterious Record Updates
                    IsaacKnoflicek

                    Good news, I stumbled upon the cause of this issue and figured I'd share and maybe you can help me figure out the best course of action.

                    What happens is every time you backdate an Invoice it triggers a script which does a "Replace Field Contents" setting Invoice::Date on all related Invoice Data::Date fields.

                    It makes sense that this should happen for any Invoice Data records related to the current Invoice, but it seems to do this for EVERY invoice and subsequently touches every record in Invoice Data.  Is that by design or did I mess up this script or some companion piece which caused it to function incorrectly?

                    Thanks,
                    Isaac

                    • 7. Re: Mysterious Record Updates
                      philmodjunk

                      I'd take a closer look at the script involved.

                      Here's a bit of a "wild guess" scenario:

                      Your script does a Go To Related Records step and then does a Replace Field Contents.

                      If there are no related records to "go to", the script does not change layouts and does not change the current found set. Replace Field Contents then is performed on the current layout and on the current found set--possibly changing every record in your table.

                      Whenever your script uses GTRR to pull up a found set and then modifies the records in that found set, you need to either check for the existence of related records before executing the GTRR If [ Not IsEmpty ( RelatedTable::IDField ) ] or use Get ( LastError ) to check for a problem immediately after the GTRR.

                      But the reason this modified every record could have a completely different cause.

                      • 8. Re: Mysterious Record Updates
                        IsaacKnoflicek

                        I created a brand new FM13 database using the Invoices starter solution (what mine is based off).  The only change to DB structure I did was add a timestamp on modified to the "Invoice Data" table

                        Then I created an invoice put in some junk items, created a second invoice and changed the date and it updated the "Modified" timestamp on the data items from the first invoice.

                        The script trigger is only one line with a find and replace, I've posted a screenshot below:

                         

                        • 9. Re: Mysterious Record Updates
                          philmodjunk

                          But if your script has nothing that affects the current found set. From what is shown, it could be anything from a single record to all the records in the table.

                          You need to look at what comes before the call to this script.

                          • 10. Re: Mysterious Record Updates
                            IsaacKnoflicek

                            This isn't "my script" this is the script that comes canned in the Invoices Starter Solution.  It's called as a trigger to setting the date field in the Invoices layout and it does appear to update all records in the table.  I'm not sure if this is by design or if this was overlooked by whoever designed that starter solution.

                            • 11. Re: Mysterious Record Updates
                              philmodjunk

                              Nevertheless, it's your script now. To fix the issue, it's up to you to analyze the scripts and figure out how/why it is failing. We can help, but only up to a point as we can't see your data and the current design state of your file. What I was trying to point out is that you need to next look at the circumstances that control when this script is performed. If a script trigger performs this single step script, then you'll need to take a look at what might be the found set of the current layout at the time that trigger is tripped. If perform script performs this script, you'll need to look at what the script does immediately prior to performing this single step script to see if there might be an issue there.

                              • 12. Re: Mysterious Record Updates
                                IsaacKnoflicek

                                Fair enough.

                                So it looks like when you create a new Invoice there is no found set, or rather it's showing all records.  So I'm guessing that's what the 1 line script is working against, and that's why it updates all related records?

                                Would I be better off limiting the found set to just the current record, or is there a better script step to use to modify just records related to the current record?

                                Thanks for your help.

                                Isaac

                                • 13. Re: Mysterious Record Updates
                                  philmodjunk

                                  A found set of all records would be consistent with what you report.

                                  Would I be better off limiting the found set to just the current record, or is there a better script step to use to modify just records related to the current record?

                                  Which need to be updated? The single parent record or a set of records related to it?

                                  If you never need to update more than the current record, don't use replace field contents. Set Field can be used to make the same change, but only on the current record. If you need to update a set of records, your script needs to either perform a find or perform a Go To Related Records script step to produce the needed found set prior to using replace field contents to update them.

                                  One danger to Go TO Related Records that needs to be addressed. If you set up this script:

                                  Go To Related Records [
                                  Replace Field Contents

                                  You can get disastrous results should there be zero related records for the current parent record. In that case, the script leaves the current found set and layout unchanged and proceeds to execute the Replace Field Contents script step as though the Go To Related Records step does not exist. This can modify a completely different group of records and from the context of a completely different layout. To avoid this, the script should check for the existence of related records before performing the GTRR step:

                                  If [ not IsEmpty ( RelatedTable::PrimaryKeyField ) // any field in the related table that is never empty can be used here ]
                                     Go To Related Records [

                                  or you check for an error code immediately after the GTRR step:

                                  Go To Related Records [
                                  If [Get ( LastError ) = 0 ]