Value Prompt for multi-value fields

[tomj55]

I have a field that can contain any combination of values 1 through 10.

Example:  A database that contains 5 records with a multi-value field with the following values:
                         
                    Rec 1: '1'    Rec 2: '5,8'    Rec 3: '6,7'    Rec 4: '1,6,9,10'    Rec 5:  '3,4,8'

I need to create a multi-select value prompt that will return any records containing the selected values in the field. So, if I select 1 and 9 for the value prompt , records  1, 3, and 4 should be returned since they contain the values 1 and/or 9.

Any takers?

Thanks.

[MFGF]

That's a very unusual storage form you are using.  It is not even at First Normal Form.  I can't think offhand of any mainstream reporting tools that will handle this easily - they are all designed for use against data stored in at least Second Normal Form (star schemas) or Third Normal Form (snowflake schemas/transactional databases).  Your multi-value fields sound very much like the old Pick/Prime Information/Reality paradigm, none of which were relational.

If you can't change the storage format of the data, you're going to have to get inventive.

1. The filter - you could try using a filter with syntax such as:

[multi value field] contains ?param?

Problems I can see - if your parameter contains a value of (say) 1, you run the risk of returning a record with containing a value of 10 or 11

2. The prompt - it will be tricky to have the prompt values populated by a query, as the values are all in one field.  Is it feasible to cade them all as static values?

If you can't get past these issues, then you need to think seriously about pivoting these multi-valued fields into multiple rows of data, whcih would then make reporting easy.

Good luck!

MF.