Forms Version 11.0.2311.50556 Classic Designer.
I have a table with a couple fields on it, including a field for "Member Number" (our version of an account number). There is another field on the table (a hidden field) that determines the thousand number range of that member number. For example:
- 1234 calculates to 001000-001999
- 12345 calcualtes to 012000-012999
- 123456 calculates to 123000-123999
This is used when archiving to the repository at the end of the process, as our records are segmented by thousands like this.
This is the formula I'm using:
=RIGHT("000000" & ROUNDDOWN(INDEX(member_list_table.member_number,ROW())/1000,0)*1000, 6) & "-" & RIGHT("000000" & SUM((ROUNDUP(INDEX(member_list_table.member_number,ROW())/1000,0)*1000),-1), 6)
Basically, it's taking the member number, dividing it by 1000, rounding up or down to get the top and bottom of the range, subtracting 1 from the top of the range, and then appending leading zeros as needed.
This formula has been in place on this form for several years without issue, and I have not seen errors with it in that time. But in the last month I've have two instances where users encountered it giving them errors. In both cases, the issue was that row 1 of the table worked, but row 2 was trying to get the member number from row 3, and row 3 was trying to get the member number from row 4, etc. Either INDEX or ROW seems to be having an issue in these cases.
I haven't updated Forms since about 5 or 6 months ago, so I don't think this was a recent change to Forms to cause this behavior - though it could be possible.
I haven't been able to replicate this error despite multiple attempts, and I can't determine anything I am doing differently than the users who encountered the error were doing.
Has anyone ever encountered an issue like this - where a formula using the INDEX and ROW functions are targeting the wrong row of a table? I'm just kind of stumped on this one.