Nintex Forms for Office 365 set cascading lookup filter in RS using cascading lookup from other RS


I am attempting to set a cascading dropdown names City in an Expense Claim RS using the Destination cities from a Travel Request RS



 



Scenario:



 A request is made to travel to the following destinations



 





 



The Expense Claim RS Country field should only display Guatemala and Australia, which would filter the city to Flores or PERTH WA



 





 



This is not possible using rules and I believe that JavaScript would help however although  I am familiar with JavaScript I wouldn't class myself as able to write anything even close to accomplishing this requirement. I need some pointers and assistance with JavaScript if help can be provided please?



 



Thanks



Chet



 


5 replies

Hi Chet,



 



what is the actual issue here?  Is it that when you select the country, the next lookup doesn't automatically select a city from that country?



 



If you're trying to get it pick a city automatically, isn't that kind of dangerous when it comes to something like expenses or travel, because it would let the user be lazy and not set the actual city themselves, which would result in potential errors in the data they are submitting.



 



What if you had a rule for the city and the country, that they were "Required" fields and the user must select them, themselves.  That would reduce the number of submissions that had mistakes in them.



 



eg. What if everyone who was travelling to Guatemala, had "Flores" in their city, but half were actually going to Guatemala City, but they didn't pick that because the form looked like it was completed?

Hi @vadim_tabakman 



 



Thank you for reaching out :)



 



I need to ensure that only the Destination cities that the employee is travelling to can be selected,



 



This is to ensure that when they are specifying days and entitlement, the Per Diem rate is accurate in order to prevent an employee claiming more than they are entitled to. 



 



In the example below the Per Diem Rate differs between Cordoba and Marsa Matrouh, then the sub total is calculated using the following formula 



 



sum(if(Breakfast, 0.2, 0) + if(Lunch, 0.3,0) + if(Dinner, 0.4,0) + if(Incidentals, 0.1,0)) * Days * PerDiemRate)



 



as Entitlement equates to a percentage of the Per Diem Rate -  Breakfast 20%, Lunch 30%, Dinner 40%, Incidentals 10%.



 



In Cordoba the Employee has specified that they will only be taking Breakfast and Lunch so 50% of $75 * 5 days can be claimed where as for Marsa Matrouh 100% of $38 can be claimed for the 2 days



 



I hope this makes sense and explains why employees must only choose cities that they are travelling to.



 





I look forward to your advice to resolve this issue



 



Thanks



Chet



 

Are the Country and City drop downs, Choice or List Lookup controls?

They are all list lookup columns

Hi @vadim_tabakman @burked 



 



Thanks for all your help on previous posts



 



I believe I may have worked around this by dragging the Per Diem repeating section into the Travel Itinerary as below and linked the Per Diem Rate calculation directly to the Destination City Field which in this case is Bridgetown.



 





 



Few questions -



 





  1. How do I copy the section above which is for Travel Authorisation to the Travel Claim below? As you can see it is identical however the named controls all begin with "TC" (Travel Claim) instead of "TA" (Travel Authorisation) eg Flight Type = TAFlightType above and below TCFlightType - I'm happy to do this using workflow just not sure how to go about it.


  2. How to set the PerDiem Date to the Date of Departure


  3. How to ensure Date of Departure can only be added as a future date, I know validation works but that's only when attempting to save the form, I need it as an OnChange event - obviously not a JavaScript developer but I know how to apply it in the form. 




Thanks for your help



Chet



 



 



 

Reply