Format Rules with parent and child conditions

Dario · 07-07-2020 12:48 AM

Hi everyone, sorry for the numerous posts, but I figure its best to get these issues out the way as I haven’t been able to problem solve them yet. This one in particular relates to child references in the UX->Format Rules section.

As you can see in the image attached I have formatting rules relating to a parent table (Employees), but the only condition I am able to use for the ‘related refs’ virtual columns is whether the virtual column is Blank or not. While this is nice, I wish to have more granular filtering, for example whether the child rows in this virtual column have a date range between ‘x’ and ‘y’, or if the child rows have a ‘verified’ status. So for example this is my current format rules expression that only takes into account whether the child records are completely blank or not:

OR(ISBLANK([Related Appointmentsrefs]), ISBLANK([Related Trainingrefs]), ISNOTBLANK([Related Warningsrefs]))

Is there anything I can do to achieve this, or is it not possible in the UX-> Format Rules section?

Best wishes,
D

Suvrutt_Gurjar

I am sure you will have better guidance from other community members.

Here is my understanding. I believe, in general, you may be able to build just about any format rules expressions, by pulling values from child tables or parent tables.

However in general, one may not have very complex format rule expressions as per my understanding. Complex rules have potential to slow down the app responsiveness. Please refer last section in the article below and also the post below.

Steve

See Constructing a List from a List Dereference here:

See also:

Dario

Thanks Steve, I hope this will be the solution I am looking for. I’m only just wrapping my head around simple dereferences, nevermind listed ones (lol). Will this section also help me with that other issue I was talking about at the beginning of my post? (I linked to it - about the child references in attachment reports).

Dario

Steve, I have been working through this method and its quite uneven. I have had success pulling the child reference using a ‘ENUMLIST’ column type with a base type as REF, however as soon as I try use a SUM formula on that child list I get an error saying “The expression is valid but its result type ‘Number’ is not one of the expected types: EnumList” .

The original values making up that child list are set as numbers, so it cant be that. I’ve attached an image which hopefully makes it all a bit more clear…

Steve

SUM() produces a single value, not a list of values.

I have a feeling you’re going about all this entirely wrong, but I’m also not entirely clear what you’re trying to accomplish. Using plain language, please describe what do you want the format rules to do.

Dario

The format rules is actually separate to this last comment, so I apologize for the confusion. It was related to my earlier question linked that didn’t get any hits but was still fixed by the advice you provided. I did see another user post where you suggested a SUM for a ENUMLIST with a basetype set to REF, so thats why I thought it may be possible to achieve.

Edit: Also Steve, if I add an extra column just to sum this working enumlist expression (e.g SUM([Timesheetref3]) it gives another error (The ‘SUM’ function requires a list of numeric inputs)

Edit2: If this is true and ENUMLISTS with REF basetypes are indeed fragile, then I can just use an expression as a workaround, its no biggie. I just thought using REFs would be the most proper way of going about it.

Edit3: Gonna avoid using ‘List Dereference’ to sum Enumlists, as the select formula seems to work well enough. Since the tables are linked by a key value the SELECT expression only needs to reference that key value… nice and simple. SUM(SELECT(Timesheetref[Weight Prod], [Timesheet] = [_THISROW].[KEY]))

Edit4: Another reason that you may want to use the SELECT instead of the List Dereference is due to the former having a UNIQUE option, when you place TRUE at the end of your Select formula. This returns (and even sums) list items only according to their unique values, very helpful when a form is auto filled with existing information but you don’t want that information to mess with your totals.

1minManager

I’ve had it before where ISBLANK() doesn’t work as expected when used with lists. Try COUNT()=0 instead

Dario

I didn’t realize ISBLANK could cause these kinds of issues. Thanks for the tip.