Hi,
I have a problem where a value prompt is limiting the available options from the source. The source field is pulling from a calculated field. When I try manually reordering the calculated field, the prompt then shows the "new" top two options. I'm expecting the prompt to show me 7 options.

You cannot view this attachment.

Quote from: tstanley on 21 Dec 2023 01:40:24 PMHi,
I have a problem where a value prompt is limiting the available options from the source. The source field is pulling from a calculated field. When I try manually reordering the calculated field, the prompt then shows the "new" top two options. I'm expecting the prompt to show me 7 options.

You cannot view this attachment.

Hi,

When using a case statement or if/then/else statement, processing normally stops once a "true" result is returned. This means it's not a reliable way of using it to populate a list of values for a prompt. I would be tempted to code the prompt values as static values in the properties of the prompt, and maybe add a comment to the report to let other report authors know that the prompt values and case calculation should be updated in tandem.

Cheers!

MF.

Thanks, MF! That would probably explain why I can get away with using a case statement in the pPractice prompt because they are never going to overlap, but in the issue I'm having here there are overlapping visit type values.

Quote from: MFGF on 21 Dec 2023 01:54:46 PMHi,

When using a case statement or if/then/else statement, processing normally stops once a "true" result is returned. This means it's not a reliable way of using it to populate a list of values for a prompt. I would be tempted to code the prompt values as static values in the properties of the prompt, and maybe add a comment to the report to let other report authors know that the prompt values and case calculation should be updated in tandem.

Cheers!

MF.

Unfortunately, this seems not to have completely worked. While it does give me an entire list of the prompt values, it completely changed what the associated metrics would look like. To visualize, made a list of the visit type code and the calculated field and realized that your point about first instance of "true" would stop the case statement, meaning that a given code will only ever be associated with one particular exclusion list.

Any thoughts as to what if I created a calculated field for each of the exclusion lists. Then create a calculated field based on that to prompt from?

Quote from: tstanley on 21 Dec 2023 02:47:04 PMUnfortunately, this seems not to have completely worked. While it does give me an entire list of the prompt values, it completely changed what the associated metrics would look like. To visualize, made a list of the visit type code and the calculated field and realized that your point about first instance of "true" would stop the case statement, meaning that a given code will only ever be associated with one particular exclusion list.

Any thoughts as to what if I created a calculated field for each of the exclusion lists. Then create a calculated field based on that to prompt from?

I think we need to look carefully at the logic you are using here. Think of a single row of data coming in to the report. When the calculation is performed for that row, what value do you want the calculation to deliver? Your current case statement allows for potentially many different results, but a single calculation in a single row of data can only deliver a single value.

For example, if the row contains a Visit Type Code of 7100014, what should be returned by the calculation? It looks like all the tests in your case statement would return a True result - should it just be the first True result?

Can you explain what you are trying to achieve?

Cheers!

MF.