8 Replies Latest reply on Jun 7, 2016 8:13 PM by sam0723

    Calculation between two portals

    sam0723

      Hello, everyone, I am new to filemaker. I would like to know how to set up a portal calculating the difference between two portals.

       

      For example,

       

      Portals

       

      Date                        Time               Time used/hrs

      1/1/2016                  9:00                    9

      1/1/2016                 18:00                   9

      1/2/2016                  9:00                    11

      1/2/2016                  20:00                  11

       

      how could i get the result every two portals? Thank you

        • 1. Re: Calculation between two portals
          mikebeargie

          You're going about time entries the incorrect way. What happens when someone creates a third entry for 1/2/2016?

           

          In your portal table, you should just have three fields, Date (of time), Time In, Time Out.

           

          You can then create an auto-enter calc field that subtracts Time In from Time Out on the same line:

          PortalTable::TimeSpent = PortalTable::Time Out - PortalTable::Time In

           

          You could then transform the result of that into hours for your display:

          Hour (PortalTable::Time Out - PortalTable::Time In)

           

          Note that calculation will round down to the nearest full hour, for something more accurate, the calculation would be more difficult.

          • 2. Re: Calculation between two portals
            sam0723

            Thank you Mike.

             

            Actually, I am working on the attendance database. I took the data from the software. Once export the data in excel format, the data are like,

             

            2016/1/1    9:00

            2016/1/1   18:00

            2016/1/2    9:15

            2016/1/2    18:00

            ......

             

            so now comes the question, could I import these raw data directly to filemaker and do the analysis? or

            I should analyse the raw data first before importing the data to filemaker?

            • 3. Re: Calculation between two portals
              mikebeargie

              If you can import the data as a timestamp, or separate date/time fields into filemaker and they are formatted correctly from the excel source, then you can format it correctly.

               

              However, it still is in the wrong format, with no guarantee as to validity that one "check in" = one "check out". So you may have to manually correct many records in your data.

               

              Have you analyzed your data to see if it is indeed only TWO records per day? That's what I would correct and be 100% sure of in excel before you try and work with the data in FileMaker. You can create a script that will change the data into the correct format in filemaker, but the script could potentially break and throw off all remaining records if just one "set" of two records is off (only has one record, or three records).

              • 4. Re: Calculation between two portals
                sam0723

                Thank you Mike. So I need to do the analysis first before importing data to filemaker, right?

                There is no way to import data directly to filemaker and then analysis the data.

                • 5. Re: Calculation between two portals
                  mikebeargie

                  Please read my response fully, I already answered that question...

                  You can create a script that will change the data into the correct format in filemaker, but the script could potentially break and throw off all remaining records if just one "set" of two records is off (only has one record, or three records).
                  • 6. Re: Calculation between two portals
                    user19752

                    +1 for MIke, but if you really want to show 2 lines for every day in portal, there may be ways for it.

                    I feel same "Time used" in 2 lines is something redundant, isn't this better?

                    1/1/2016                  9:00                    18:00                   9

                    • 7. Re: Calculation between two portals
                      sam0723

                      Ok. Thank you

                      • 8. Re: Calculation between two portals
                        sam0723

                        Thank you. user19752