You are viewing limited content. For full access, please sign in.

Question

Question

Weird inconsistent issue with Field Function to compare two fields.

asked on May 18, 2022 Show version history

Background: I am trying to use Same_First_Name_=IF(Employee_First_Name=NV_First_Name,"Yes","No") to compare both First Name fields with Employee_First_Name being filled in by the user when completing the initial Form and NV_First_Name being auto-filled by a Workflow with the information from the Employee_First_Name field.

The point of this is so that a different field shows up if this field and 2 others (For middle and last name as well) are set to yes.

The issue: Even if the fields match Same_First_Name_ doesn't set itself to Yes. If I type over either or both Employee_First_Name and NV_First_Name it stays as No, even if Copy Paste is used. However if I set both fields to a single letter T then it switches to Yes and if I add imothy to both fields it will go back to Yes once done, but Same_First_Name_ won't go to a Yes unless I start them with a matching single letter to correct it.

After some testing I do find that if I can trick Same_First_Name_ into a Yes then after it runs the workflow again and loops back it will stick to a yes.

 

If there something I'm doing wrong, or do I need some sort of delay?

Edit : Some more testing done and I've tried using Same_First_Name_ =EQ(Employee_First_Name, NV_First_Name) and swapped the rules to look for TRUE rather then Yes but it doesn't seem to change much, it does look easier for it to 'update' so changing the information inside of the NV_First_Name to something wrong and then back to the correct answer and it will update itself without needing anything to be changed on the Employee_First_Name field like I did with the =IF statement. I have no clue if this is the issue but it's like the Set Business Process Variables aren't being applied until the Form loads or something so the =EQ function doesn't update for some reason.

Name issue 1.png
Name issue 2.png
Name issue 4.png
Name issue 3.png
0 0

Answer

SELECTED ANSWER
replied on May 18, 2022

From what you are explaining, I think it still makes sense to have Workflow update the fields to be set to Yes.

You have the workflow setting these values each time:

NV_First_Name=%(RetrieveBusinessProcessVariables_Employee_First_Name)
NV_Middle_Name=%(RetrieveBusinessProcessVariables_Employee_Middle_Name)
NV_Last_Name=%(RetrieveBusinessProcessVariables_Employee_Last_Name)

 

Which means every time the Workflow runs, those three fields are updated, and at that point, the fields match, so the three "same?" fields should be saying Yes immediately.  They would only need to change to No if the user made changes to the fields when the form came back to them.

So I think you could update your Workflow to populate those fields like this: 

NV_First_Name=%(RetrieveBusinessProcessVariables_Employee_First_Name)
NV_Middle_Name=%(RetrieveBusinessProcessVariables_Employee_Middle_Name)
NV_Last_Name=%(RetrieveBusinessProcessVariables_Employee_Last_Name)
Same_First_Name=Yes
Same_Middle_Name=Yes
Same_Last_Name=Yes

 

That way, after the Workflow is run, you should be seeing the fields showing that Timothy and Timothy match=Yes, N/A and N/A match=Yes, Holton and Holton match=Yes.  Then if you change one, say the first name, it'll update the formula to show that Timothy and T match=No.

1 0

Replies

replied on May 18, 2022 Show version history

I just tried to replicate, using fields with default values and manual fields, and your formula worked for me.  Using Forms 11 Update 2.  I tested in both the new editor and the classic.

So I'm not thinking your formulas are the issue.

That being said, I recommend using the EXACT formula rather than IF - instead of "Yes", "No" it'll just return TRUE/FALSE, but it's a little more concise of a formula to write: =EXACT(Employee_First_Name, NV_First_Name)

But, regardless, I don't see any reason why either your IF formula or an EXACT formula should not be working for you (barring the possibility that you are on a different version that had a bug I'm not aware of).  I think it's more likely that Workflow is populating extra hidden characters in there, like a line break at the end, that may not be obvious.  You could try using the Inspect Element function of your browser to see the actual value of the field to see if there is anything obvious.

1 0
replied on May 18, 2022 Show version history

Wait - is your form being loaded already filled in from a prior submission and from Workflow?  If so, then the issue might just be that it isn't realizing that it needs to run the formulas.  One of the fields would need to be changed for it to trigger that it needs to run the formula to check.

If that is the case, and you are using the classic editor, you might be able to trigger it to try the formula when the form loads, with some Javascript like this (replacing q1 with the q-number of your Employee_First_Name field): 

$(document).ready(function () {
  
  //Trigger change of the Employee_First_Name field upon
  //the form first being loaded, to trigger the formula.
  $('#q1 input').trigger('change');
  
});

 

1 0
replied on May 18, 2022

I tried that code but it doesn't seem to do anything, Employee_First_Name is q1 based on the stuff on the side of the javascript/CSS screen. I've also changed to =EQ(Employee_First/Middle/Last_Name, NV_First/Middle/Last_Name) 

So the process is

Employee Fills out Form X

Runs a Name Verification Workflow

If the information provided in Employee First, Middle and Last Names does not match an existing Folder in the Repository then it does a Set Business Process Variables with 
Possible_Matches=%(Possible Matches)
Workflow_Response=%(Workflow Response)
NV_First_Name=%(RetrieveBusinessProcessVariables_Employee_First_Name)
NV_Middle_Name=%(RetrieveBusinessProcessVariables_Employee_Middle_Name)
NV_Last_Name=%(RetrieveBusinessProcessVariables_Employee_Last_Name)

Starts a User Task for the Initiator with Form X where they can correct the name if it's incorrect or submit a Start Date for the user and Submit the Form X. (Start Date only shows if Workflow Response is 0 and Same_First/Middle/Last_Names_ are TRUE)

Also I am just short-handing all 3 similar fields to First/Middle/Last rather than typing out all three fields each time.

0 0
replied on May 18, 2022

Hmmm...  I'm still not seeing anything that would explain why the formulas don't appear to be working for you.

But...  Since it seems that your Workflow is already ensuring that the NV name fields match the other fields (since the Workflow is actually populating the one set of fields from the values in the other), we know that after the Workflow completes that all of the fields match and thus the formulas should be evaluating as Yes or TRUE.  So I think perhaps you should have Workflow populate Yes or TRUE into those fields.  Then I'm hoping, any changes would ultimately result in the formulas working properly to change to No or FALSE if they no longer match.

1 0
replied on May 18, 2022

The way that my process is built allows for a manual indefinite loop in case the user somehow keeps mistyping the field and clicking the "Changes made, check again" (Approve) option to send it back to the WorkFlow so it does keep the TRUE FALSE Flags between loops too but the issue I have even if it would auto update on a minor change would be that it would require the user to make a change of somekind regardless if one is required. If the user is new and they know they aren't already listed in the Repository then they should ideally be able to simply fill out the Start_Date field and click "Add to Laserfiche".

If it helps this is what I am essentially trying to make happen. I want the Start_Date field to only appear when the process has confirmed the employee doesn't currently have any files in the Repository and the name fields haven't been modified on this loop. If the user changes the name in anyway to cause it to not match the last provided name I want the Start Date to disappear until the process has been run again. All new users require a Start_Date which requires the user to either have the Employees start paperwork on hand or reach out to Payroll. However if they realize they just mistyped it and they want to correct the name and have the process check again I don't want them hunting down a Start_Date for a user who doesn't need one, or more likely, input incorrect or made up information to get the process going. 

0 0
SELECTED ANSWER
replied on May 18, 2022

From what you are explaining, I think it still makes sense to have Workflow update the fields to be set to Yes.

You have the workflow setting these values each time:

NV_First_Name=%(RetrieveBusinessProcessVariables_Employee_First_Name)
NV_Middle_Name=%(RetrieveBusinessProcessVariables_Employee_Middle_Name)
NV_Last_Name=%(RetrieveBusinessProcessVariables_Employee_Last_Name)

 

Which means every time the Workflow runs, those three fields are updated, and at that point, the fields match, so the three "same?" fields should be saying Yes immediately.  They would only need to change to No if the user made changes to the fields when the form came back to them.

So I think you could update your Workflow to populate those fields like this: 

NV_First_Name=%(RetrieveBusinessProcessVariables_Employee_First_Name)
NV_Middle_Name=%(RetrieveBusinessProcessVariables_Employee_Middle_Name)
NV_Last_Name=%(RetrieveBusinessProcessVariables_Employee_Last_Name)
Same_First_Name=Yes
Same_Middle_Name=Yes
Same_Last_Name=Yes

 

That way, after the Workflow is run, you should be seeing the fields showing that Timothy and Timothy match=Yes, N/A and N/A match=Yes, Holton and Holton match=Yes.  Then if you change one, say the first name, it'll update the formula to show that Timothy and T match=No.

1 0
replied on May 18, 2022

I looked at this with Timothy on a remote session and we did confirm that data populated by workflow are unseen to the calculations engine, they must be populated by a human with the form open in their browser.

The reason he is not making the comparison in WF is that he wants to compare if the names match continously while the user is working in their task within forms, not just 1 time.

We wrote a javascript function to make the comparisons instead, but I think calculations should be able to see data populated by workflow, this is unintuitive.

2 0
replied on May 18, 2022

So Matthew's idea works. No idea why. I just made a copy and tested it and the Start Date field seems to update as needed after the Same_X_Name_ fields are set to TRUE with Workflow. I'm going to play with this some more and see if I can figure anything out as to why hard setting them to TRUE fixes the issue. 

Just in case though, I'm going to include the Javascript that Chad made (at it also solves the problem) so it may help anyone else with a similar issue.

$(document).ready(function () {
  
  $('.name input').on('change',CheckOriginalName);
  
});

function CheckOriginalName(){
  
var hide = 'No';
    
    if($('.originalFirst input').val() != $('.firstName input').val())
      hide = 'Yes';
      
    if($('.originalMiddle input').val() != $('.middleName input').val())
      hide = 'Yes';
      
    if($('.originalLast input').val() != $('.lastName input').val())
      hide = 'Yes';
        
      $('.hideDate input').val(hide).change();
    
}

with originalFirst/Middle/Last being classes applied to the Employee_X_Name fields the User fills out and first/middle/lastName being applied to the hidden NV_X_Name

1 0
replied on May 18, 2022

I think @████████ is right that it is unintuitive that the form doesn't try to run the calculation when it first opens in order to check the fields that were populated by Workflow. 

But unfortunately, that isn't how it works, and it requires that user-initiated change to trigger the evaluation of the formula.  By updating it in Workflow you're starting with the formula result that we know is accurate from the Workflow, so the formulas only need to be evaluated when the user makes a change.  So that's why that works.

The alternate solution Chad provided with the Javascript works because it entirely bypasses the formula functionality and builds its own process to evaluate the equality.

This is one thing I love about Forms, that there is always more than one way to solve a problem. smiley

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.