Forms - lookup rule - fill form field if match is found, otherwise fill with all values

asked on January 25, 2022

Hello,

I have a situation in which I want to fill a form field with data from a lookup table in two different ways. I'll explain the best I can.

This is a daily vehicle inspection form. Most of the time, drivers drive the same vehicle each day. We have a table in a SQL database that contains a row for every vehicle number, and I've added a column to that table called "LastDriver." When this inspection form gets completed, I have a workflow grab the driver name and the vehicle number from the form and update that vehicle's row with the latest driver name. Easy enough so far.

I want the inspection form to provide a list of vehicle numbers for users to pick from - we're doing this now with a simple lookup rule to the same table discussed above. But, if the Driver Name field on the form matches with the LastDriver column in our table, I'd like to fill the Vehicle Number field on the form with the vehicle number from our table.

I can already see some issues with this. We could find a match in our table, but the driver has a different truck today and needs the full list. So I guess in short, my question is can I fill a field with a lookup rule if a matching row is found, but also somehow provide a full list of the data from the same column?

Thanks!

0 0

SELECTED ANSWER

replied on January 25, 2022 • Show version history

Here's what I would do:

Keep your current list as-is with the full list of vehicles
Add a second, hidden, single line field for "latest vehicle" and lookup
Set the second lookup to pull the "exact" match based on driver (make sure you're using a query that can only pull one result, so it might have to be a stored procedure or view)
Add javascript so when the "exact match" is updated it auto-selects that value from the list.

That way, they still get the full list if they need to change it, but it will default to the "latest vehicle" when there is a match.

Here is an example of the javascript where the "latestVehicle" class is added to the hidden single line field and the "vehicleList" class is added to the full list field.

$(document).ready(function(){
  $('.latestVehicle input').on('change', function(){
    if ($(this).val() != ''){
      $('.vehicleList select').val($(this).val()).change();
    };
  });
});

3 0

replied on January 25, 2022

Great stuff, Jason. I was messing with a hidden field but this does what I'm looking for. I will need to account for the same driver being the 'last driver' on multiple vehicles, but I should be able to figure that out.

Thanks!

0 0

Question

Question

Forms - lookup rule - fill form field if match is found, otherwise fill with all values

Answer

Replies

Sign in to reply to this post.