Datalist query with multiple "child" and "or" operator

Ask the community
1

Hi,

Company: C1, Division: D1
Company: C1, Division: D2
Company: C2, Division: D2
Company: C3, Division: D2
Company: C3, Division: D3

Text5 = Company
Text13 = Division

I have the above data in Datalist and I use code to generate the below filters for Datalist query
“Filters”: [
{
“Field”: “Text5”,
“Value”: “C1”,
“Type”: 0,
“Child”: [
{
“Field”: “Text13”,
“Value”: “D1”,
“Type”: 0
},
{
“Pre” : “or”,
“Field”: “Text13”,
“Value”: “D2”,
“Type”: 0
}
]
},

{
    "Field": "Text5",
    "Value": "C3",
    "Type": 0,
"Pre" : "or"
    "Child": [
                {
                    "Field": "Text13",
                    "Value": "D2",
                    "Type": 0
                },
                {
                    "Pre" : "or", 
                    "Field": "Text13",
                    "Value": "D3",
                    "Type": 0
                }
             ]
},    
],

I would like the filters behave the SQL:
WHERE (Company = ‘C1’ AND (Division = ‘D1’ OR Division = ‘D2’))
OR (Company = ‘C3’ AND (Division = ‘D2’ OR Division = ‘D3’))

I expect the result should be

Company: C1, Division: D1
Company: C1, Division: D2
Company: C3, Division: D2
Company: C3, Division: D3

but now the result also include
Company: C2, Division: D2

May I know how to set the filters to achieve my expected result?

Thanks a lot

Best regards,
Andy

2

You can try below filter:

[
	{
		“Pre” : “or”,
		“Child”: [
			{
				“Field”: “Text5”,
				“Value”: “C1”,
				“Type”: 0
			},
			{
				“Pre” : “and”,
				“Field”: “Text13”,
				“Value”: “['D1'],['D2']”,
				“Type”: 9
			}
		]		
	},
	{
		“Pre” : “or”,
		“Child”: [
			{
				“Field”: “Text5”,
				“Value”: “C3”,
				“Type”: 0
			},
			{
				“Pre” : “and”,
				“Field”: “Text13”,
				“Value”: “['D2'],['D3']”,
				“Type”: 9
			}
		]	
	}
]