2 Replies Latest reply on Aug 1, 2017 4:48 AM by danielfarnan

    Indexed Data vs Non-Indexed Data in finds.


      Here is a very over-simplified example




      WORKORDER db:  wo_id, bill_id


      LABOR db: wo_id


      BILLING db:  bill_id


      LABOR to WORKORDER by wo_id


      WORKORDER to BILLING by bill_id




      Find un-billed labor


      OPTION 1:


      Labor will have a text field "status" (defaulted to OPEN)


      When you bill a WO a script will find all related labor and post BILLED to labor status field.




      CONS: Slow to perform find. Opportunity for failure if a labor line is in use elsewhere.



      OPTION 2:


      Labor will have a calculated status field based on if the related WORKORDER has been billed.


      PRO:  100% reliable, fast


      CONS: Finds on an unindexed field can be very slow.  Overtime it become intolerably slow, like when you exceed 200K records.




      What do you tend to do?

      Is there a third option? 

        • 1. Re: Indexed Data vs Non-Indexed Data in finds.

          The calculated status field could also be indexed by setting it via a script when the data it relies on changes.  I tend to set any fields like that using a script so that there aren't any critical un-indexed fields. That keeps the speed.

          • 2. Re: Indexed Data vs Non-Indexed Data in finds.

            I'm not sure if it qualifies as a third option, but I have in the past set up both a calculated field and a regular field with an auto-entered calculation or lookup that fires every time the record is modified.


            Also, to mitigate your slow finds with your option two you can create your find requests differently - FileMaker isn't clever enough to do a multiple pass search every time on its own (yet), so do it manually by only searching on indexed fields first, then constraining the found set with your criteria for the unstored field(s). That way you're minimising the chance of needing to poll every record for a value. It usually helps to make a scripted find process for these situations.


            And for option one, it helps to treat a status update for the parent record as a transactional activity. You can find a bit more information here, but the gist of it is that you want the process to either complete 100% successfully or fail. You first try to obtain a lock on the parent record; if that succeeds you go through each portal row for the child records and try to update each one. On any failure, revert the parent record and all related records will also be reverted. Otherwise, commit the parent record and all related records will also be committed.