13 Replies Latest reply on Feb 15, 2012 11:14 AM by RonCates

    Finding a values position

    RonCates

      Title

      Finding a values position

      Post

       In a variable I have a return seperated list of Employee IDs. From an employee record, how would I find out what position an employee's ID holds in that list ?

        • 1. Re: Finding a values position
          philmodjunk

          You could loop through it with exit loop if conditions that exit the loop when either the ID is found or the end of the list is reached. A counter variable can be used with getvalue to access specfiic ID's in the list and the counter variable will tell the position when the loop is exited.

          • 2. Re: Finding a values position
            RonCates

             I was trying to pull the position into a calculated field. Is that possible?

            • 3. Re: Finding a values position
              philmodjunk

              It could be done with a custom function that uses recursion to loop through the list. A recursive calculation field might be able to do this as well. If you have advanced the custom function is a much better option.

              I can also imagine a possible calculation that does this, but which only works if there is a maximum number of items permitted in the list. If the length of the list is open ended, the calculation will fail once the list reaches a certain length. What I am imagining would be best for lists of just a few items in length.

              Case ( EmployeeID = GetValue(EmployeeIDList ; 1 ) ; 1 ;
                          EmployeeID = getvalue(EmployeeIDList ; 2 ) ; 2 ;
                          EmployeeID = GetValue(EmployeeIDLIst ; 3 ) ; 3 )

              and then if you have a list of 4 and the sought for ID is in the 4th line, nothing is returned by the case function...

              • 4. Re: Finding a values position
                RonCates

                Found a Custom Function called " FindInList " on Brian Dunnings sight that does the job. Now I'm trying to use this in a repeating calculation field, but it's only working on the first repetition.

                 

                 

                Let ([

                pos = FindInList ( $$EmpIDs ; _pk_employee_id ) ;
                string =Substitute ( GetValue ( $$CountTickets ; pos ) ; "|" ; "¶" )]  ;

                GetValue ( string ; Get ( CalculationRepetitionNumber ))

                )

                 

                $$EmpIDs is a return seperated list of IDs

                $$countTickets is a return seperated list of "|" seperated values

                1|4|56|23|
                |5||35|10
                12||25|20|1

                Can I make this work in a repeting calc field?

                • 5. Re: Finding a values position
                  philmodjunk

                  As I take it, you are using the position of Employee ID in $$EMPIDs to extract text from $$CountTickets in the same position in its list. If the employee ID is the second value, string gets:

                  5

                  35
                  10

                  Where's the repeating calculation field that's involved in this? From what little I can find in help, I don't thinkg Get ( CalculationRepetitionNumber) is going to return anything but 1 here. What value do you want returned at that point?

                  And this all seems like a way to use calculations to extract data that would be much more simply done via related tables and relationships.

                  • 6. Re: Finding a values position
                    RonCates

                     It's for a complicated crosstab report. I'm trying to display different data with different repetitions. CalculationRepetitionNumber returns the repetition number of the calc field.

                    If I set up the calculation as

                     Get ( CalculationRepetitionNumber )

                    and put an instance of the field on the layout for each repetition it shows the repetition number being displyed.

                    • 7. Re: Finding a values position
                      philmodjunk

                      Yes, but what does that have to do with the values in your lists?I don't see any repeating calculation and can't picture the connection between it and the calculation that you have here. Is this expression, the repeating caculation?

                      I use filtered portals to produce cross tab style reports, BTW.

                      • 8. Re: Finding a values position
                        RonCates

                         well this is the complete explaination of the process thus far

                        http://fmforums.com/forum/topic/82454-help-with-a-crosstab-report/

                        I've been having trouble getting that site to come up off and on today so I was trying to work it all out and hoped that a few quick answers here could help get me there.

                         

                        • 9. Re: Finding a values position
                          RonCates

                           

                          Let ([
                          pos = FindInList ( $$EmpIDs ; _pk_employee_id ) ;
                          string =Substitute ( GetValue ( $$CountTickets ; pos ) ; "|" ; "¶" )
                          ]  ;

                          pos

                          )

                          and

                           

                          Let ([
                          pos = FindInList ( $$EmpIDs ; _pk_employee_id ) ;
                          string =Substitute ( GetValue ( $$CountTickets ; pos ) ; "|" ; "¶" )

                          ]  ;

                          string

                          )

                          In both cases the results only showed in the first repetition. It would appear that Let() in a repeating calculation field only fires in the first repetition?

                           

                           

                          • 10. Re: Finding a values position
                            RonCates

                             I tried rewriting it without Let()

                             

                            GetValue ( Substitute ( GetValue ( $$CountTickets ;  FindInList ( $$EmpIDs ; _pk_employee_id ) ) ; "|" ; "¶" ) ;  Get ( CalculationRepetitionNumber ))

                             

                            But still only returns results for the first repetition, so it's not the Let() function :(

                            • 11. Re: Finding a values position
                              philmodjunk

                              What did you specify for "number of repetitions" in this calculation?

                              Since your are using variables, I suspect that they need to be extended before you will see a result in each field.

                              Haven't used repeating calculations in YEARS, so I'm a bit rusty here.

                              Extend ( Let (.... put what you already have here ) )

                              I what I am recalling as necessarry here. I used to use this to apply the value of a not repeating field to every repetition in a repeating field if I am remembering the syntax correctly.

                              • 12. Re: Finding a values position
                                RonCates

                                 Sorry Phil. I was not trying to drag you into a long project here. I was able to get the site to come up and found that Comment had provided the answer

                                "You need to use Extend() around _pk_employee_id."

                                Thanks for your help

                                • 13. Re: Finding a values position
                                  RonCates

                                  This is the first time I've ever used repeating fields at all so I'm in a learning process. Which is partly why I chose to use the repeating field technique for this.