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

# Finding a values position

### 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

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

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

• ###### 3. Re: Finding a values position

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

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

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

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

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

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

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

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

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

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

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.