Table lookup with comparison value

asked on February 25, 2019

I have an approval process where we want the submitter to be able to select he approver based on dropdown, but only display users that have that approval level.

Submitter would select Dept from a drop down list, and fill in the approval amount field.

Once both fields have been entered, from a different dropdown, select the approver they want to send it to. There can be multiple approvers at a particular level.

I could easily do this in Process Modeler with a logical gateway, but they want to select on the form. I am familiar with the field lookup, but it only works for matches and does not have the ability for a comparison.

I do not have a JavaScript background.

Forms 9.2

Any help is appreciated.

0 0

replied on February 25, 2019

It sounds like you might want a stored procedure for that kind of logic. For example, instead of looking at the table directly, create a stored procedure that accepts the department and approval level as input values, then return a list of all values that match the department and have an approval level equal to or greater than the input.

1 0

View 1 previous reply

replied on February 28, 2019

Our process admins do not have access to our SQL servers either. When a need like this arises the SP is added by someone with administrative rights.

Unfortunately, Forms does not have less than or greater than type comparisons in the lookups so there's no way to make this work out-of-the-box.

Any solution in Forms itself would require multiple tables with separate lookups, and probably some custom JavaScript, to get what you want.

0 0

replied on February 28, 2019

ok, I appreciate the help. thanks for responding. I am going down the sp process now. that will get me functional.

I guess I need to document the process that my process admins need to go through to request this kind of assistance, and that they know what to ask for specifically.

0 0

replied on February 28, 2019 • Show version history

Realistically, you will need a process like this anyway because adding a data source to a form requires System Administrator privileges inside of Forms.

It is unlikely you would want your Process Creators to have those rights, so inevitably they'll need to put in a request when they need a data source created or added to a new form.

We used Forms to create a "Change Request" process that gives users a clear guide for what information is required for various changes and routes tasks to those with admin rights.

0 0

Question

Question

Table lookup with comparison value

Replies

Sign in to reply to this post.