Using an Array when bounds are unknown

[CarolCook]

Hi:
This is the SQL data table I am using for my report with a couple of sample rows.

Document Field Name FieldValue (memo)

Salesorder Salesman 1
Check Salesman 24

Many different document types, many field names, many identifiers.
The identifiers are keys into other tables that hold the description.

Salesman

Key Name
1 Joe Jones
45 Mary Smith

The report needs to look like this:

Document Field Name Name
Salesorder Salesman Joe Jones
Check Salesman Mary Smith


I was going to build an array first to populate the identifier but I don't know the upper bound and it changes over time - so the salesman table shrinks and grows. Once I get the array, each field would have to have a for loop which might make this take forever.

 

[pmax9999]

Unless I am missing something, I think the simpler approach would be to join the Salesman table to the other table on the Key value and add the following line to the Record Selection formula:

{Table.FieldName} = "Salesman"

Hope this helps.

Cheers
Pete

 

[CarolCook]

Thanks for answering Pete:
The problem is that the foreign key value is a memo field. This is to accommodate other types of info such FieldValue = date or some other hard coded value. What I wound up doing was to create a formula which converted the memo to a number for salesman and created a subreport that went out and grabbed the value. It is not ideal because it doesn't show the value if the salesman is the first record in the table.

 

[CarolCook]

Also, I will need to create reports that bring in any document with all its values but only for one salesman.