Filter based on two out of three possible values

[NoahWasserman]

I need to filter based on two similar types of data. I've got "Inspection type 1", "Inspection type 2", and "Maintenance" as options for a Type field.  I need to be able to filter by "Inspection type 1" or "Inspection type 2" so I can calculate when the next inspection will need to be done. I previously had one inspection type so to filter I used...

var type = "Inspection"

var relatedfeatures = Filter(related,"Report_type = @type")

...but now I have two. They'll be used in the same way, but I need to be able to list all inspections.  How do I put two different variables into "Report_type = @type"?

[Teresa_Blader]

I don't have time to build you out and test a full code... but I wonder if you could write an if else statement for which Filter expression gets used. Likeif (inspection type 1 == type) [relatedfeatures = filter(related, "Report_type" @type1)] else if (inspection type 2 == type) [relatedfeatures = filter(related, "Report_type" @type2)] else relatedfeatures = ''
 
(define type1/type2 variables first) 

I probably have the variable names in the wrong spot, not sure where "related" comes from or "Report_type" or which table your inspection fields are in. But maybe the concept is worth playing around with.

[NoahWasserman]

Here is my code...I'm hung up on creating a large if structure when there could be a simple solution:

//pulls inspection frequency in days

var last = $feature.InspectFre

var adddays = Decode(last,"Annual", 365, "3 years", 1095, "Weekly", 7,0)

//establishes a start date for anything that doesn't have a previous inspection

var start = Date(2023,0,1)

//Pulls all inspection reports for this site

var ordered = OrderBy(FeatureSetByRelationshipName($feature, "InspectionReports"), "i_site_date DES")

var site = $feature.DBID

var related = Filter(ordered, "DBID = @site")

//there are three options, 'maintenance', 'septic inspection', and 'holding inspection'. The same calculation happens below, but I want to update from the singular 'Inspection' to either 'septic inspection' or 'holding inspection' so we pull just the inspection reports, order them, and count them.

var type = "Inspection"

var relatedfeatures = Filter(related,"Report_type = @type")

//calculates next inspection date

var info = First(relatedfeatures)

var cnt = Count(relatedfeatures)

var relatedinfo = iif(cnt > 0, Text(DateAdd(info.i_site_date,adddays,'days'), "MM/DD/Y"), Text(DateAdd(start,adddays,'days'), "MM/DD/Y"))

return relatedinfo

[Teresa_Blader · Accepted solution]

Ah, I see. So you want the filter to ask "if it contains the word "inspection"", correct? vs "if it equals"... since the different types are all in one field.

I think you would write Filter(related,"Report_type LIKE '%inspection%'"). Filter uses SQL92 language... but I'm not quite sure if that would be... LIKE '%inspection%' or something else.... if that's the case you'd skip var type and just write out the expression maybe? SQL is always a little tricky to me! 🤔

[Teresa_Blader]

Maybe you could alternatively write Filter(related, "Report_type = 'septic inspection' || Report_type = 'holding inspection'")

|| is OR in if else statements... might need to be OR in Filter SQL92

[NoahWasserman]

Thank you. I think this is working.  For anything with %Inspection it looks up last inspection, for anything 'maintenance' it adds the inspection frequency to 1/1/2023.