Conditional lookup rules

asked on September 4, 2015 • Show version history

As the forms we build become more complex, we find ourselves needing correspondingly more robust lookup rules that are based on conditions.

For instance, one of our customers have a PO form where they need to determine if the expenses for a project are taxable based on the job number entered. Here's the tricky part: if the job number starts with 1, tax information needs to be looked up on Table A. If it starts with 2, it needs to be looked up on Table B. This is outside our control as it's how their accounting software is designed.

Unfortunately, the lookup rule conditions are very basic right now and they only allow "if field value matches value in database field" type rules. We need more types, such as those based on regular expressions or even hardcoded values. Also, it would be nice to be able to dynamically adjust the values coming in from the database before they populate the fields (e.g. convert the value to lowercase, multiply it with a number, etc.).

Is this currently in the works?

0 0

replied on September 7, 2015 • Show version history

Hi there!

I guess a way around this would be to use javascript to check/un-check a 'taxable' check box depending on the job number entered using a script something like this:

// When job number has been entered...
$(document).on('blur', '.job_number input', taxable);

function taxable() {
// Check if job number equals 1
if ($('.job_number input').val() == 1) {
// If true check the taxable check box. If not un-check it
$('.tax_check_box input').prop('checked', true);
} else {
$('.tax_check_box input').prop('checked', false);
}
}

You could then use the lookup rules to see if the 'taxable' box is checked. However I agree that it would be really handy to have this functionality built into the lookup rules dialogue!

P.S. I haven't had chance to check this script in forms so there might be a couple of mistakes!

0 0

replied on September 7, 2015

Thanks. I actually ended up going with another approach, which was to create a database view that combines the two tax tables, run a lookup on that based on the job number entered, then have conditional logic in JS that checks the lookup results to see if that job is taxable.

The logic goes like this:

1. Job numbers starting with "1" follow the ap_tax_exempt column in tax_table_A

2. Job numbers starting with "2" follow tax_table_B, but they also have sub-categories: if they are category 1, they follow column 1, if they are category 2, they follow column 2, etc.

So my fields (inside a collection) look like this:

job number
job category
tax status
ap_tax_status (hidden)
tax_table_B_category_1_tax_status (hidden)
tax_table_B_category_2_tax_status (hidden)

When the user enters the job number, the hidden tax status fields get populated if they have corresponding values. If they don't, they remain blank (since those fields are NULL in the database view). Then the user picks the category. At this point the JS logic that runs every second to see if conditions match does the job: if job category is 1 and the tax_table_B_category_1_tax_status has a value, the visible tax status field gets marked with Yes.

Not the most elegant solution, but the best I could come up with.

0 0

Question

Question

Replies

Sign in to reply to this post.