Editable dropdowns from lookup rules that may contain null values not populating as needed

asked on March 1, 2022 • Show version history

Hey everyone,

I'm experiencing something I have been using very manual work arounds for, but found that my workaround isn't foolproof either. The gist is that I can't find a way to get my dropdown fields fully flexible in it's environment. The values need to all be there regardless of lookup, and it should auto select only if it finds results - otherwise it should be blank.

My use case:

Form that mostly populates with data to assist user with historical references (if it exists), and if no historical data exists, the dropdowns should be blank and the user fills in with regular choices.

My issue:

For an example, let's boil this down to it's simplest form. A dropdown containing Yes or No values. Let's say the lookup rule pulls 'Yes' for this dropdown field. Without Append Results checked, that would be the only choice available for this field. If the user would want to then change this to 'No' it would have to come from the choices built within the field itself.

So far so good. We have a dropdown with Yes/No values inside it that also populates from a lookup rule that appends its values to the dropdown.

The first issue after appending values is it does not auto select the dropdown when it pulls in the results. Rather it appends it to Option 1 in the dropdown. So lets take a look at a few examples in action.

Case1: Lookup Rule finds 'No' for result. Choice for No moved to spot 1

Case2: No results found, dropdown list reverts to it's default choices within the field.

Case 3: Result it finds in lookup rule is empty (not null, but empty)

Going off these results I decided that it was best to do what others have done, and use javascript to selectindex 1 after lookup rule has run.

Snippet of code I'm using for a portion of these dropdowns:

$(document).on("lookupcomplete", function (event) {
  //Set baseLookup to ruleID of lookup 0 in api and it will update all rules appropriately
  var baseLookup = 7957;
  if (event.ruleId == baseLookup) {
  $("#Field6, #Field12").prop("selectedIndex", 1).trigger('change');
    console.log("base " + event.ruleId);
  }
  //API Lookup 1
    if (event.ruleId == baseLookup +1) {
  $("#Field86, #Field87, #Field95, #Field97, #Field94").prop("selectedIndex", 1).trigger('change');
      console.log("1base " + event.ruleId);
  }

This makes it work in all cases where it will start selecting even with appended results; except, when there are no results, or the results found are null. When this is the case, it will always just auto select 'Yes' as it is the first value of the dropdown and that can be highly misleading.

Potential solutions:

JavaScript solutions

Is there a way in JavaScript to track lookup rule results? If I can find a selector to access that information I can update my JavaScript to only selects index 1 IF the lookup was not null. I was digging through the jsonForm in browser debugger and was not able to find the right selectors to use to see if a lookup rule had results or not.

There is also the jQuery EditableSelect library which turns single line fields into dropdowns; however, I tried this too and it was only populating with results from the database and did not have an obvious way to add line items to my database results.

Another thought I have not pursued is to maybe instead of choosing 'Append choices to lookup results', I add the options to each dropdown after the lookup rule has run through JavaScript? That way if it does find something it will be auto selected by default and the whole: select index 1 can be avoided. I'm assuming the root of the behavior not auto selecting is due to the fact that there are multiple choices. So if the lookup rule runs first and the choices are added after - then maybe that will work too?

SQL solutions

Forcing my views or stored procedures to pass an empty value instead of a null value in the cases where there are no results, or the results it finds are null?

Forms solutions
1. Add a single line text field for every dropdown
2. Switch the lookups to those single line fields.
3. Have JavaScript (since formulas dont exist on dropdown fields) read each value, and if it is not empty, update the associated dropdown's selected value to what matches in the single line text.
4. Somehow prevent JavaScript from changing user's input if the lookup rule gets triggered again (I believe I read something about changing the field's attribute to ('vo', 'k') to accomplish this?)

$('#q2 input').focusout(function(){
    $(this).attr('vo','k');
  });

Other solutions

There could be something glaringly obvious that is out there that maybe some of you know. If there are workarounds you've done or think will work, I would greatly appreciate any of your input. This seems like such a small and simple thing to have sunken so much time into.

Thank you!

2 0

SELECTED ANSWER

replied on March 2, 2022 • Show version history

If your values are always going to be Yes/No, that will help because it makes them predictable and what I would do in scenarios like this is to put the lookup result is a separate hidden field.

Then, set an on change event handler for the lookup field that will select a value from the static dropdown if/when it is populated.

For example,

Add a CSS class called maximumStackHeight to your dropdown, and a class called maximumStackLookup to your hidden field.

Have the lookup populate the hidden field and leave the dropdown static.

Then use JavaScript like so,

$('.maximumStackLookup input').on('change',function(){
    $('.maximumStackHeight select').val($(this).val()).change();
});

Because the lookup isn't interacting directly with the dropdown, you don't have to worry about null or blank values.

Even if the "lookup" field is populated with white space like " " it won't hurt anything because if you try to set a dropdown with a value that isn't in the options, it will blank it out.

However, you could add .trim() to the end of $(this).val() to be sure, or you could add an IF condition that only updates the value if the lookup isn't blank or whitespace.

Yet another option, if you don't want to have separate lookup fields, would be to create a view or stored procedure on the database side that sanitizes the outputs for you.

For example,

SELECT NULLIF([Column],'')
FROM [Table]

SQL should ignore trailing whitespace with a NULLIF comparison, so '', ' ', ' ', etc., should all return a NULL value with that select.

1 0

replied on March 3, 2022

Jason,

Thank you for this. I didn't want to have to add a lot of single line fields, lookup their FieldID's and such, but your code is much more elegant than what I was planning. I also found during this that it is not an issue within my collections - so I can just ignore all those.

Going by css class is superior as I won't have to lookup those id's, I can make short work of copy pasting, and it is much easier to reference especially if I give the classes the same names as my variables/DB columns.

Progress is on the way and here is the start of it -

$(document).on("lookupcomplete", function (event) {
  $('.xstackTP_Board_Cond input').on('change',function(){
    $('.stackTP_Board_Cond select').val($(this).val()).change();
});
    $('.xTP_Drop_Test_Plastic_Frozen input').on('change',function(){
    $('.TP_Drop_Test_Plastic_Frozen select').val($(this).val()).change();
});
    $('.xdropFiberboardConditioned input').on('change',function(){
    $('.dropFiberboardConditioned select').val($(this).val()).change();
});

It is working well! Cheers.

0 0

Question

Question

Editable dropdowns from lookup rules that may contain null values not populating as needed

Answer

Replies

Sign in to reply to this post.