Lookup Runtime Function Filtering on Two Columns

  • 9 March 2022
  • 6 replies
  • 12 views

So I have one list name Electronic Filing Credentials for ECF, my formula is added to a calculated field I have a people picker column call HHR Name and a date column called "Date PAA Added and nested within the lookup is a userprofile lookup. So essentially in my Nintex form you put a person's name in People picker that date shows up. The formula looks like this and it works fine.



lookup("Electronic Filing Credentials for ECF", "HHR Name" , (userProfileLookup(HHRName, "PreferredName")) , "Date PAA Added")



 



Now the problem is that Date PAA Added field isn't always filled out.  A user can have multiple entries in the list and depending on another column called "Credentials Type" (a choice field with three choices, PACER, State, and Federal) depends on if that date field is populated. And if any of the fields are blank then the formula does not work and that calculated field is blank.



 



So basically the ask is can I also filter within that formula based on that Credentials Type column if it contains Pacer that it will show up in the calculated field. Hope this makes sense. Thank you in advance.


6 replies

So is this not possible? Anyone?
Userlevel 4
Badge +16

@Jake  Could you please assist here?

Yes please @Jake your expertise would be greatly appreciated, thank you.

Userlevel 3
Badge +8

Hi @ctoper11 



 



Would you be able to send me an export of the form and also an example of the lookup data in a csv? I don't want real world data but something I could use to perform the lookup?



 



I think it should be possible but you may need to nest lookups or alter the data set to facilitate the lookup.



 



Thanks



Jake 

@Jake thanks so much for your help. Here you go

Hi @Jake have you gotten a chance to look at my list and form? Thank you for your help.

Reply