10 Replies Latest reply on Nov 3, 2015 3:02 AM by ChristofferRexen_1

    Get latest salary from Portal

    ChristofferRexen_1

      Hello everyone,

       

      I got this portal, see attached picture, for our pension department.

       

      The portal is used for entering salary on our customers.

       

      Now, when a salary have been entered, a calculated field is calculating the total pension contributions.

       

      The calculated field goes like this:

      Last ( Pension_Employee_SalaryPortal::YearSalary ) * Pension contributions (// A nummeric field ) / 100

       

      Now the problem is, that I can't get the calculated field to 'get' the last created salary in the portal, it just keeps calculating based on a former salary.

      The portal is sorted after SalaryCreatedDate

       

      Any advice?

      Regards

      Christoffer

        • 1. Re: Get latest salary from Portal
          Mike_Mitchell

          Hello, Christoffer.

           

          The Last ( ) function draws the record based on the sort order of the relationship, not the portal. If the relationship is not sorted, it will draw the most recently created record. In order to do what you want using Last ( ), you'll need to set the relationship to be sorted on the Relationships Graph. (Note that this may invoke a performance penalty if the number of related records grows large.)

           

          HTH

           

          Mike

          • 2. Re: Get latest salary from Portal
            siplus

            I presume you have the yearNumber in the Pension_Employee_SalaryPortal.

             

            you can define lastYearlySalary as

             

            Let (

             

            ly = ExecuteSQL("SELECT Max(yearNumber) FROM Pension_Employee_SalaryPortal WHERE EmployeeID = ?";"";""; EmployeeID);

             

            ExecuteSQL("SELECT YearSalary FROM Pension_Employee_SalaryPortal WHERE EmployeeID = ? AND yearNumber = ?";"";"";EmployeeID; ly)


            )

            • 3. Re: Get latest salary from Portal
              ChristofferRexen_1

              So.. I tried Mike_Mitchell's function.

              Works - although, not on older-created Salaries.

              Now the employees in the pension department is also complaining about the sort-order, newest salary is on the bottom of the Portal and vice-versa.

               

              So, I tried Siplus approach, but not really the master of art to SQL.

               

              Let (

               

               

              ld = ExecuteSQL ( " SELECT Max ( ÅrligGageDatoOprettet )

                                            FROM Pension_Medarbejder_Gage

                                            WHERE MEDARBEJDER ID MATCH FIELD = ?";"";""; Pension_medarbejder_Gage::MEDARBEJDER ID MATCH FIELD ) ;

               

               

                    ExecuteSQL ( " SELECT ÅrligGage

                                            FROM Pension_Medarbejder_Gage

                                            WHERE MEDARBEJDER ID MATCH FIELD = ? AND ÅrligGageDatoOprettet = ?";"";""; Pension_medarbejder_Gage::MEDARBEJDER ID MATCH FIELD ; ld )

               

               

              )

               

              It just end out in a ?...

               

              Any help?

              • 4. Re: Get latest salary from Portal
                siplus

                WHERE MEDARBEJDER ID MATCH FIELD


                must become


                WHERE \"MEDARBEJDER ID MATCH FIELD\"

                • 5. Re: Get latest salary from Portal
                  ChristofferRexen_1

                  So.. Goes like this?

                  - Sorry, as said, not a big shark to SQL.

                   

                  Let (

                   

                   

                  ld = ExecuteSQL ( " SELECT Max ( ÅrligGageDatoOprettet )

                                                FROM Pension_Medarbejder_Gage

                                                \"WHERE MEDARBEJDER ID MATCH FIELD\" = ?";"";""; Pension_medarbejder_Gage::MEDARBEJDER ID MATCH FIELD ) ;

                   

                   

                         ExecuteSQL ( " SELECT ÅrligGage

                                                FROM Pension_Medarbejder_Gage

                                                \"WHERE MEDARBEJDER ID MATCH FIELD\" = ? AND ÅrligGageDatoOprettet = ?";"";""; Pension_medarbejder_Gage::MEDARBEJDER ID MATCH FIELD ; ld )

                   

                   

                  )

                   

                  It still comes up with a ?

                  • 6. Re: Get latest salary from Portal
                    Mike_Mitchell

                    ChristofferRexen_1 wrote:

                     

                    So.. I tried Mike_Mitchell's function.

                    Works - although, not on older-created Salaries.

                    Now the employees in the pension department is also complaining about the sort-order, newest salary is on the bottom of the Portal and vice-versa.

                     

                     

                    What do you mean, it "doesn't work" on older salaries? What are you sorting on?

                     

                    If the sort order is bothersome to the users, reverse it and just use relatedTable::fieldName instead of Last ( ). This will just fetch the first record in the sort instead of the last (and it'll be faster to boot).

                     

                    Native FileMaker functionality is almost always faster than ExecuteSQL because of the way FileMaker's internal queries work. And if you're not a SQL "shark", it's easier to boot.

                     

                    Mike

                    • 7. Re: Get latest salary from Portal
                      ChristofferRexen_1

                      What do you mean, it "doesn't work" on older salaries? What are you sorting on?

                      I tried sorting on a date field i made, where the users are intended to type the date the given salary goes into force.

                      Ascending order.

                      - Although, as said, previously typed in salaries doesn't seem to take effect - that said; a customer have already two salaries entered, one older than the other, now the database should calculate based on the last created salary, although it doesn't, but the first salary, unless I delete both salaries and re-enter them both.

                       

                      If the sort order is bothersome to the users, reverse it and just use relatedTable::fieldName instead of Last ( ). This will just fetch the first record in the sort instead of the last (and it'll be faster to boot).

                      I think i'm beginning to get a bit blind, or else I'm having a bad/long day..

                      Could you please elaborate on this?

                      - Sorry..

                      Native FileMaker functionality is almost always faster than ExecuteSQL because of the way FileMaker's internal queries work. And if you're not a SQL "shark", it's easier to boot.

                      Gotcha..

                      • 8. Re: Get latest salary from Portal
                        Mike_Mitchell

                        ChristofferRexen_1 wrote:

                         

                        What do you mean, it "doesn't work" on older salaries? What are you sorting on?

                        I tried sorting on a date field i made, where the users are intended to type the date the given salary goes into force.

                        Ascending order.

                        - Although, as said, previously typed in salaries doesn't seem to take effect - that said; a customer have already two salaries entered, one older than the other, now the database should calculate based on the last created salary, although it doesn't, but the first salary, unless I delete both salaries and re-enter them both.

                         

                         

                         

                        Do the salaries have the same date? If the sort is based on a value that is the same, then it will default to creation order for values that are the same. Otherwise, this doesn't make sense.

                         

                         

                        If the sort order is bothersome to the users, reverse it and just use relatedTable::fieldName instead of Last ( ). This will just fetch the first record in the sort instead of the last (and it'll be faster to boot).

                        I think i'm beginning to get a bit blind, or else I'm having a bad/long day..

                        Could you please elaborate on this?

                        - Sorry..

                         

                        You said the sort was ascending. Use a descending sort instead. Then you can just use relatedTable::salary instead of Last ( relatedTable::salary ) to grab the latest value (because the "first" value in the list will be the most recent).

                        • 9. Re: Get latest salary from Portal
                          siplus

                          I fear that the Å in ÅrligGage and ÅrligGageDatoOprettet is not something ExecuteSQL is happy with.

                           

                          try enclosing them in \"  \", too.

                          • 10. Re: Get latest salary from Portal
                            ChristofferRexen_1

                            You Sir, are a genius.

                            It solved the question, and as you mentioned is quicker than SQL.

                             

                            Thank you, for the help Mike!

                             

                            /Cheers

                             

                            Regards

                            Christoffer