1 2 Previous Next 18 Replies Latest reply on Feb 3, 2016 1:58 PM by Fahri Akar

    Calculations: Find and Match

    SLucia13

      Hello Community,

       

      I am working to develop an iOS App Solution in FM that will do the following:

       

      (1) scan barcode at Location A and save text/timestamp - 2 fields

      (2) scan barcode at Location B and save text/timestamp - 2 fields

      (3) Calculation time between timestamp at Location A and timestamp at Location B

       

      I have scripts written for steps (1) and (2) but I am not sure how to write a calculation for Step (3).  Is there a similar formula to Excel's Find/Match function in FM where I can write?

       

      Thank you!!

       

      Stefi

        • 1. Re: Calculations: Find and Match
          Blue22

          Is the application running real time or will the iOS be syncing up later?

           

          WIll the package always be going from A to B or could it go from B to A?

           

          Option 1. You could join the 2 tables based on the barcode and do timestamp math and insert the calculation. potential risk if you could end up with more than two scans of the same barcode.

           

          Option 2. You use the same table and create one record for departure and one record for arrival. You then relate the 2 TO together and do timestamp math again. Same potential risk.

           

          Option 3. Is one table with fields for barcode, departure time, arrival time, and difference. You then need  more logic in your scan to determine:

          1. Do I have a record in the table with that barcode? No - create a new record a paste barcode and first timestam.

          2  If yes, Is the second timestamp field empty. Yes - insert time stamp.

          3. If no, create a new record and set the barcode and first timestamp fields.

          Risk if someone misses a scan and the package comes back through the system before it is corrected multiple records could need to be adjusted.

          • 2. Re: Calculations: Find and Match
            SLucia13

            Wow, this is all really great information! Thank you!

             

            To answer your questions... the path will always be from A to B and as of now the data will be exported via email from the device at a later time.  Also, the risk of scanning the same barcode twice at the same location is very low since there are not duplicate barcodes and the app requires the user to press "submit" before the barcode reading is saved in the database.

             

            As for your options, I am most interested in Option 3.

             

            However I just realized I left out an important detail... The scans will be done by two users at two locations using two devices.  Is my request still possible if I had the device updating in real time?

             

            Thanks again!

            • 3. Re: Calculations: Find and Match
              Blue22

              It would just be done differently if one or both devices were in real time. The 2 different devices and locations isn't important as long as the data is all coming back to the same database.

               

              If one or both of the devices is offline then your logic would have to be in your import(s) to match existing records or create a new record if no match.

              • 4. Re: Calculations: Find and Match
                SLucia13

                interesting... do you have a reference for more information on creating a real time database?

                • 5. Re: Calculations: Find and Match
                  siplus

                  You could have a Item table and a scan table.

                   

                  A scan simply adds a new record to the scan table. (itemID, timestamp, other data)

                   

                  The item table is linked to the scan table via itemID, through that relationship you can get min(timestamp) and max(timestamp) and show the difference on the Item record.

                  • 6. Re: Calculations: Find and Match
                    Blue22

                    By real time I was talking about connecting the devices to a database served by FileMaker Server whether FileMaker Go, FileMaker Pro or WebDirect instead of running the database locally on the devices.

                    • 7. Re: Calculations: Find and Match
                      SLucia13

                      ah interesting.. I could then do a lookup to pull information off of the secondary table to the primary table?

                       

                      For example, let's say that the following table is my "Primary Table" and the data can be defined as the following:

                           Date - variable

                           Installation - variable

                           Barcode - scanned information, text

                           Scan A - timestamp via button

                           Scan B - timestamp via button

                           Time - calculation of time between two scans IF the Installation, Date, and Barcode are equal

                                      

                       

                      DateInstallationBarcodeScan AScan BTime
                      1/26/2016FA  1234510:05:0010:26:000:21:00
                      1/26/2016FA   5648510:08:5610:48:200:39:24

                       

                       

                      so youre saying that I could make a "Scan" table as a secondary table to collect the scan timestamps and then I could write a lookup formula to pull these times into the appropriate locations of my primary table?

                      • 8. Re: Calculations: Find and Match
                        siplus

                         

                        so youre saying that I could make a "Scan" table as a secondary table to collect the scan timestamps and then I could write a lookup formula to pull these times into the appropriate locations of my primary table?

                         

                        short answer: yes


                        longer answer: every scan you do adds a record to a scan table - it's like adding an item to an invoice. This also helps:


                        - reducing the data traffic, because you create a new record with little info on it

                        - avoiding record locking conflicts, because you don't mess with the main item record

                        • 9. Re: Calculations: Find and Match
                          SLucia13

                          that's really neat! good to know.  what is the name of the calculation/script that I would need to use for the lookup?

                           

                          Also, I will have two staff member collecting the data for me.. is there a way I can sync up their devices to work from the same two tables via wifi?

                          • 10. Re: Calculations: Find and Match
                            siplus

                            Each item (or parcel or whatever it is) has an unique identifier, coded in its barcode. Call it ProductID.

                             

                            When you do a scan, you read the ProductID and you also know what time it is and who is doing it (employeeID), eventually you know the coordinates (geolocation). So you create a new record in the "Scans" table, holding this data. The Scans table has a fkProductID you set to the ProductID, a ReadTimeStamp field that you set and so on.

                             

                            The Item table is linked to the Scan table via ProductID. Let's call the table occurrence ProductScans.

                             

                            In the item (parcel etc) database you have a calculation field which reads

                             

                            Let (

                             

                            scans = count(ProductScans::ProductID);

                             

                            choose(scans;"no data yet"; "in transit"; "arrived after" & GetAsTime(Max(ProductScans::ReadTimeStamp) - Min(ProductScans::ReadTimeStamp)))

                             

                            )

                             

                            how does it work ?

                             

                            the ProductScans relationship sees only the records in Scans related to the item. Therefore it can see

                             

                            - no records (no scans happened yet)

                            - 1 record (the first scan)

                            - 2 records (first and second scan)

                             

                            if more intermediate scans of your parcel occur, no problem, the relationship will see 3, 4, n scans.

                             

                            count(relationship::ID) will tell you how many scans there are.

                            Choose(parameter, value1, value2, value3...)  looks at parameter. If it's 0, it gives back value1; if it's 1 it gives back value2, and so on.

                             

                            in your starting situation, you can only have 0 scans, 1 scan (beginning one) or 2 scans.

                             

                            if you have more than 2 scans, we will change the Choose with a Case( scans = 0; "no data yet"; scans = 1; "in Transit"; timestamp calc).

                             

                            if you have 2 or more scans, the Min () and Max () functions will start to work their magic: they will look at the related data and return, well, min and max of a specific parameter, in your case the timestamp. If you have less than 2 scans, min and max will return the same value, no harm done.

                            • 11. Re: Calculations: Find and Match
                              fitch

                              The formula is simply: TimestampB - TimestampA

                               

                              That's the easy part -- how you get the data into your system is the real issue, as discussed above.

                              • 12. Re: Calculations: Find and Match
                                SLucia13

                                ha yes that one I know...

                                 

                                i'll keep looking -- appreciate all of the help!!

                                • 13. Re: Calculations: Find and Match
                                  Fahri Akar

                                  Create two serial id fields.

                                  First id field begins with 1, second begins with 2.

                                  Relate two fields than make your calc.

                                   

                                  Sample is attached

                                  Simple way.

                                  • 14. Re: Calculations: Find and Match
                                    siplus

                                    ever heard of "multiuser environment" ?

                                    1 2 Previous Next