Quick Report User Field Dropdowns

Overview

In SalesPad it is possible to populate a list of options for a user defined field using a modified version of a SalesPad Quick Report. The dropdown can either pull from a static table or pull dynamically based off of screen values.

Setup

In the User Field Editor, create a new user defined field. Set an appropriate type and length for the new user defined field (you can use this on any type, just make sure your sql returns the same results, i.e. char to text, int to int, etc.).

Once the field is created, add the Quick report to the Quick Report field.

Click the ellipsis (...) to bring up the editor.

Essential report outline includes a <report> tag and a <query> tag:

<report ReturnColumn='COLUMN NAME THAT GETS WRITTEN BACK TO FIELD'>
<query addWhere="false"> YOUR SQL HERE
</query>
</report>

After the outline is roughed in, you can start to fill in the details. Example:

<report ReturnColumn='Serial_Num'>
<query addWhere="false">
select Item_Number='', Serial_Num='' union
SELECT
FROM
s.svAssignedTo
WHERE
</query>
</report>
isnull(Item_Number, '')
,isnull(Serial_Num, '')
spv3SalesDocument as sd
join spvCustomer as c on c.Customer_Num = sd.Customer_Num join spxInventorySerialNum as s on c.Customer_Num =
sd.Sales_Doc_Num = /*Sales_Doc_Num*/
and sd.Sales_Doc_Type = /*Sales_Doc_Type*/

In this example, we are pulling values from the screen into the where clause.

WHERE
sd.Sales_Doc_Num = /*Sales_Doc_Num*/
and sd.Sales_Doc_Type = /*Sales_Doc_Type*/

The highlighted sections will get replaced at runtime with the values from the screen. Here is a list of valid replacement tags:

On Sales Document Field:

/*Sales_Doc_Num*/
/*Sales_Doc_Type*/
/*Customer_Num*/

On an Item:

/*Item_Number*/

On a Customer Addr:

/*Customer_Num*/
/*Address_Code*/

On a Purchase Order:

/*Po_Number*/

Once you have the report defined, you can navigate to the appropriate screen and click the drop-­-down to see the results: