Slice row filter condition for child record in list

[Seth_Berman]

I have an app containing a shopping cart similar to the sample app Order Capture. It has a two tables with a parent child relationship to relate orders with individual products ordered.

I am trying to create a slice of the parent table to filter out orders that have already shipped completely from those that have not had all individual products ship. I have created a column in the child table that the user will set a true flag when the product ships, this column has an initial value of false. The parent table contains the List half of the ref relationship between the two tables the ref in the child table is set with ISPARTOF.

i tried using this formula on Slice of the Parent Table, it did not error but it didn’t display anything.
IN(“FALSE”,[Child List Column][Shipped?])

[Suvrutt_Gurjar]

hI @Seth_Berman,

If I have correctly understood your requirement , please try following.

Please create a VC in the parent table with expression [Child List Column][Shipped?], Say this VC is called [Shipped Status Parent] Please base your slice expression on this VC such that the slice expression is something like

IN(“FALSE”, [Shipped Status Parent])

I believe since you are using the list expression directly in the slice filter without using it in a column, the app does not return any rows even though technically your slice expression is correct. I believe there is no column in any row that satisfies the condition, so the slice returns empty in the expression mentioned by you.

I have tested for both the conditions and it works with a VC in place as I mentioned.

[Seth_Berman]

that worked like a charm!! thank you. it even shows me which of the individual products have shipped and which haven’t, nice solution.

[Suvrutt_Gurjar]

Thank you for the update @Seth_Berman. Nice to know that the approach worked as per your requirement.

[candicepelser]

Hi Surrutt & Seth,

Perhaps you two can assist with my case - similar to your original challenge Seth.

I have a Parent table of Turtle Nests, and where they have hatched there is a Child hatching record.

I am trying to create a slice of the Nest table, for only nests that have not yet hatched.
So I tried using the already existing Virtual Column for related hatchings. The filter condition is:
ISBLANK([related dbo.hatchings])

This does not seem to filter out the nests which do have child hatching records.

Any words of wisdom?

[Suvrutt_Gurjar]

Could you please try with a slice filter condition of

COUNT([related dbo.hatchings])=0

[candicepelser]

Darn, that’s not working either.
I was so excited there for a moment…

[Suvrutt_Gurjar]

May I know the name of reverse reference column in the parent table?

[Suvrutt_Gurjar]

If the reverse reference column name is just [Related Hatchings] , then the filter expression to try is

COUNT([Related Hatchings])=0

[candicepelser]

trying that now

[candicepelser]

The name is “Related dbo.hatchings”

[Suvrutt_Gurjar]

So the slice needs to show all those parent Nest records where there are not even one child Hatching record, correct?

Then I believe it should work with the expression

COUNT([Related dbo.hatchings])=0 should work.

One way is if you are not using that Reverse reference column in any other expression, you could even rename it with say “Related Hatchings” and try with expression

COUNT([Related Hatchings])=0

You may also wish to post if feasible, screenshot of what test results you are getting with those expressions, if the problem persists.

[candicepelser]

Yes, you are correct in what the requirement is.
However, that is not working.
I tried: COUNT([Related Hatchings])=0
But Appsheet said it doesn’t recognise the Field, and recommended the correct name “Related dbo.hatchings”

Thanks so much for your assistance Suvrutt!
I will post a separate question / bug report

[Suvrutt_Gurjar]

Hi @candicepelser ,

I do not think this is a bug. I requested you to rename the column name to "Related Hatchings"and then use that name in the expression. Or else the AppSheet system will not find the column name and you are getting that error.

I suggested to change the name column only because your existing column name has a decimal point notation in its name and hence requested a name change if that is causing any issue.

In summary, whatever column name ,the expression COUNT([Column Name])=0 should typically work.

I request you to post the screenshots if feasible of test results you are getting so that one may assist with any other issue there may be.

[candicepelser]

Thanks again Suvrutt, It is working now.

Strange issue… The preview of the slice wasn’t working, but when I use the data slice in my UX, it works perfectly. Weird, but the real result is there.

I’m so grateful for your assistance. Thanks again

[Suvrutt_Gurjar]

Great to know it works as per your requirement. For the benefit of other community members who may refer this post forsimilar requirement , may I request you to post the expression you finally used.

[candicepelser]

This is the one that I used, and it works:
COUNT([Related dbo.hatchings])=0

[Suvrutt_Gurjar]

Thank you.