Is vvIn_Virtual() and Dropdowns

[gwilburn]

Is vvIn_Virtual valid for data sources for dropdowns?

I have the following data source defined for a dropdown on a form.

SELECT
sales_rep_nbr,
trim(sales_rep_nbr) || ' (' || trim(sales_rep_name) || ')' AS sales_rep_name
FROM
v_slsreps
WHERE
(
com_nbr = vvin_virtual('companyNumber', '009', 'char', 3, NULL, 'true') or com_nbr = '***'
)
ORDER BY
sales_rep_nbr

The app variable 'companyNumber' is used throughout my app. However, the drop down is only showing the two values from the "OR" in the where statement above (where com_nbr='***')

It's like the default value is being replaced
(in this case the app variable companyNumber is the default value of '009' )

[robert.swanson]

That should work fine. If you turn on logging and then look at the SQL statement it's running to fill that drop-down via Portal Admin > Logs, what does it show?

[gwilburn]

That's just it... I do not see the SQL statements for any of the drop downs... I see where every file is selected, the field value is selected, etc. But I do not see any SQL statements logged for the drop down list.

My form is setup to transform the data fields to dropdown lists using a separate data source. In the separate data source contains the vvIn_Virtual() statement.

It appears that does not get invoked.

Should I be using a form helper to filter each combo instead?

[sean.lanktree]

The combo boxes will be loaded only once when the form is created...startup of the application (assuming the form is not a popup window). If you need the combo to be reloaded again when the companyNumber app variable is changed you need to tell it to do so.
This can be done through the use of another app variable...suppose you call it "reloadSalesCombo". Hover over your form in NAB and press the "Link" button. On the left side click the "Load Data" section...then on the right side you should see the field name for your combo...select the "reloadSalesCombo" app variable. So whenever reloadSalesCombo is set to true it will reload the combo results. Now you just need to set that app variable to true when you want it to reload.

[gwilburn]

The combo boxes will be loaded only once when the form is created...startup of the application (assuming the form is not a popup window). If you need the combo to be reloaded again when the companyNumber app variable is changed you need to tell it to do so.
This can be done through the use of another app variable...suppose you call it "reloadSalesCombo". Hover over your form in NAB and press the "Link" button. On the left side click the "Load Data" section...then on the right side you should see the field name for your combo...select the "reloadSalesCombo" app variable. So whenever reloadSalesCombo is set to true it will reload the combo results. Now you just need to set that app variable to true when you want it to reload.

The companyNumber app variable is already set before the form is created. Theoretically, wouldn't the value be used by the combos when the form is created? Is it created when shown or when the app is opened?

I already had loadHeaderCombos app variable. So I tried linking that app variable to the Load Data section for that field (OHSLR#) - it's already being used for other combos on this form. I then set it true when the section is shown.

Same results... my combo box only contains the two values where com_nbr = '***'

I have several combos on this form that I would like to filter based on the value in the companyNumber app variable.

What am I missing?

[sean.lanktree]

If your combo has data then the call has to be available in Portal Admin > Logs (assuming logging is turned on). It should be called upon creating the application and then anytime that you set your "loadHeaderCombos" to true. Are you absolutely certain that you are not seeing this call being made in the logs?

[gwilburn]

Got it!

I was not looking back to when the app opened. I didn't realize that the data sources get loaded when the app is first created.

I found it...

Your method is working now. Thank you!

[sean.lanktree]

OK good...just to confirm your combo box is now loading the data that you want?

[gwilburn]

OK good...just to confirm your combo box is now loading the data that you want?

Confirmed.

Part of the issue was bonehead. My test companyNumber does not have any valid Sales Reps :rolleyes:

It should not occur... but if the value on an existing order is not in the list, it displays an "error" when viewing (not editing) the form.

Is there a way to display the field value even if it is not valid in the drop down?
(I'm not sure this is 100% necessary)