Solved: Initial value depending on preselection in a relat...

Ksenia1 · 08-10-2021 01:55 AM

Hi all,

I need help with an expression due to my rather complex data structure.

The App has a table for buildings (Gebaeude).
Each building can have multiple cases (table Case) and rooms (table Raum).
Each of these rooms has several elements.
As one element can have several cases and vice versa, this gets combined in the in-between table CaseElement.

In the App I navigate through the Building_detail view to its Room_Detail view to its Element_Detail view where the user can add a Case to the Element by adding the CaseElement.

Now, if I want to add a CaseElement to my Element, I want the latest added Case for the same building as the element is in to show up as my initial value.

How do I do it if my Element table doesn’t have the immediate ref to the building table but I have to go through the room table first?

WillowMobileSys

Ok. Using the MAXROW(), the expression would need to be modified like the below AND since it can only return a single row (or none), I think the expression can be simplified into a single MAXROW()…I think like so :

MAXROW("Cases", "CreatedDate",
           AND([Building]=[_THISROW].[ElementId].[Building]), [Status] <> "Completed")

MAXROW() can optionally use a third parameter to filter. In this case by Building and Status. It will then return the [Case ID] of the row with the max [CreatedDate] from that filtered list. If the filtered list returns no rows, then MAXROW() will return an empty value.

View solution in original post

WillowMobileSys

It sounds as if you open Cases only on Elements. If that is so, then I would recommend a slight relational hierarchy change

Buildings → Rooms → Elements → Cases

These would all be setup as Parent/Child relationships - i.e. flip on the “Is part of” flag.

For convenience of display and based on your problem description, I would also recommend adding into the Element table columns for Building and Room. When adding a new case, assign these columns “silently” using the “dot” notation method to follow back up the tree.

For example, let’s say tables below include AT LEAST these ID and Ref columns:

Buildings table - [Building ID]
Rooms table - [Room ID], [Building] 
Elements table - [Element ID], [Building], [Room]
Cases table - [Case ID], [Building], [Room], [Element]

With this hierarchy, when it comes time to create a new Cases row, you can use “dot” notation to silently assign the ID and the Parent and Grandparent Ref’s like so:

[CaseID] = UNIQUEID()
[Element] = automatically assigned on the Add action of a child Cases row
[Building] = [Element].[Room].[Building]
[Room] = [Element].[Room]

FYI, with this hierarchy, you would always be able to use the “dot” notation to retrieve the Building and Room values - even if those columns were NOT included in the Cases table. Including them is just convenience.

Additionally this approach allows you to easily gather Cases by Room and/or by Building should you ever want/need to do so.

Ksenia1

Thank you @WillowMobileSystems for your detailed response! I did as recommended and added the Grand- and Grandgrandparent columns so it will be easier to refer to. Though, Cases will not be opened on Element. The structure looks as follows:
Buildings → Floors → Rooms → Elements → CaseElements ← Cases (with ref to Buildings) ← Orders

One Order can have many Cases and one Case can have many CaseElements. CaseElements shall assign a Case to an Element so in the end, one Case is broken down into an amount of Elements that need to be maintained. The mechanic can change the status of each CaseElement and if everything is done there, the Case itself can be set done.

I am still struggling with the correct expression so when filling out the CaseElement_Form it’s initial value for CaseId is set to the latest Case where Element and Case have the same BuildingsId.

WillowMobileSys

Awesome! I understand your structure better now.

You actually have 2 distinct trees. A Buildings tree and an Orders tree. There would be no “family” relationship between the two trees. Rather you simply have references - i.e. Cases references Buildings, CaseElements reference Elements filtered by the Building in the Parent Case row.

I do understand you wish to go directly to the CaseElement_Form from the Element row. Then automatically assign the CaseID based on the case having the same Building Ref.

But I have two questions:

What do you expect to happen when NO Case exists for that Building?
Can there be more than one ACTIVE Case for the same Building?

But to continue on the “happy path”…

Generally, when you perform the action of adding a CaseElement from the Element row itself, you would do this with a custom action to launch in the CaseElement_Form using the LINKTOFORM() function. You would then be able to preset the Form fields using expressions. For eample, you could preset the CaseID column in the CaseElement row with an expression like this:

ANY(SELECT(Cases[CaseID]. [Building]=[_THISROW].[Building]))

Note: this assumes you have a column named “Building” in the Element row. if you do not then you would use “dot” notation to trace back up the tree - [Room].[Floor].[Building].

The ANY() function will return the value from the first row. If there can be only a single row, then you’re good.

If there can be more than 1 Case row per Building, then there needs to be a secondary way to select the correct Case out of that list.

If its possible that a Case row might NOT exist, then you’ll want to decide how to handle that. Most likely this means creating the Case row first but the big question is what process flow do you decide to use.

Ksenia1

Then the user can add a new Case.

Yes, that’s possible. Also, Column Case in CaseElement has a valid if: Case Status <> “completed”.

I am slightly confused. I can open the CaseElement_Form from the Element_Detail View and this way the Element Column will be preset.

Right. That’s when I need to add something like:
MAXROW("Case", "CreatedDate") in combination with that Case[Buildings] = Element[Buildings] comparison.

WillowMobileSys

Based on the above from your previous post. The relationships are not clear. But, since I now know more about your data structure, it feels more “natural” for CaseElements to be a child of Cases. So my post was based on this assumption.

I started a big response but let’s just walk through this step by step.

First, How is CaseElement related to Elements? Do you have a Parent/Child setup between the two? Or is Element simply a Ref column on the CaseElement table?

Second, how do you launch into the CaseElement_Form?

Ksenia1

You got me there. What is the difference between a Parent/Child setup and simply a Ref column? Element is a Ref column in the CaseElement table. So far, “Is a part of” is not checked but might be.

Comming from Element_Details view and adding Related CaseElements.

At the same time, the CaseElement Form shall not be filled out comming from the Case View. So, CaseElements are visible in the Related section in Case_Details but adding them from that side is not possible.

WillowMobileSys

Sorry, Parent/Child does mean that the “is part of” is turned on. I think you do have that on since the Element is automatically added into the Form when you click “Add”.

Ok, in this use case you have outlined above, you can use the Initial Value property to automatically select the Case row. it would be similar to what I suggested above - BUT - there are some edge cases to contend with. So here is the STARTING Initial Value I would suggest:

IF(
   COUNT(SELECT(Cases[CaseID]. 
         AND([Building]=[_THISROW].[ElementId].[Building]), [Status] = "Active") ) = 1, 

   SELECT(Cases[CaseID]. 
         AND([Building]=[_THISROW].[ElementId].[Building]), [Status] = "Active"), 

   ""
)

If there is only a single Case row, then assign it, otherwise leave the Case field blank. This can be expanded with any decisions you make for the Edge Cases below.

Edge Cases

What should happen if there are MORE than 1 Active Case with the same Building? How do you choose which Active Case should get assigned? If there is an automated way, then we can include those details into the expression above. OR you could simply show a dropdown list for the user to choose from
When there are NO Active Cases for the Building, you mentioned before the user will need to add one. Where do they go to add it ? The point being that a user may go into Element Detail and attempt to click on the Case Element add button - see 3 below for more on this.
More importantly, when no Active Cases, what do you want to have happen in the Element_Details Form for the Case Elements “Add” button. To me, it doesn’t seem prudent to allow them to even attempt to add a Case Element, if they then need to go elsewhere in the app to add a Case row first. There are several ways to handle this in a seamless way. I can offer suggestions here if you wish.

Ksenia1

It is off. It seems like it doesn’t need to be activated to refer to the correct Element when adding the related CaseElement coming from Element_Detail.

WillowMobileSystems:

IF(
   COUNT(SELECT(Cases[CaseID]. 
         AND([Building]=[_THISROW].[ElementId].[Building]), [Status] = "Active") ) = 1, 

   SELECT(Cases[CaseID]. 
         AND([Building]=[_THISROW].[ElementId].[Building]), [Status] = "Active"), 

   ""
)

We are getting close. First thing I realized is that I didn’t have a column in my CaseElement table to retrieve the BuildingId. So I added that and the formula [ElementId].[BuildingId].

I guess I don’t need to look for the correct status in initial value when I have a valid if formula to exclude the not open ones, or am I wrong?
Valid if = SELECT(Case[Id], [Status] <> "completed")

So, in the initial value, besides comparing the BuildingIds, I want to look for the latest Case according to its CreatedDate column. Something like MAXROW("Case", "CreatedDate").

Thus, edge #1 will be resolved as always the latest will be pre-selected:

As regards to edge #2 and #3 the user can add a new Case from CaseElement (by selecting the CaseId) if the Case has not been filled yet.

Normally, Cases get added by management beforehand so that mechanics just need to go to the correct address/room and change the CaseElement status of each Element after being done with their work. A new Case would only have to be added by the User (e.g. mechanic or janitor) if for example a sudden problem with the Element has been found whilst being on-site.

WillowMobileSys

Yes, now that I think more on it, you are right!

Yes, you will still need to filter by Status. The Valid_If only controls what is seen in the dropdown. It will not impact the expressions in the Initial Value.

An alternative is to create a Slice that filters out the “Completed” cases and then use that Slice in both the Valid_If and Initial Value. You will probably find you need that Slice in other places as well so will be a worthwhile investment to implement (it’s easy!)

Correct! If you add criteria that selects the MAXROW() case by CreatedDate AND Building, then the Initial Value will be all set.

Very good! That is another way to handle it.

Sounds like you are almost there. Let me know if you need any additional help.

Ksenia1

At the moment it works and only Cases are shown in the dropdown which are not completed.

I just cannot get the expression right and that is what I need help with. So, I need an expression for my initial value where the BuildingId of CaseElements gets compared to the BuildingIds in Cases and from the list of CaseIds that match that condition give me out the latest

WillowMobileSys

Ok. Using the MAXROW(), the expression would need to be modified like the below AND since it can only return a single row (or none), I think the expression can be simplified into a single MAXROW()…I think like so :

MAXROW("Cases", "CreatedDate",
           AND([Building]=[_THISROW].[ElementId].[Building]), [Status] <> "Completed")

MAXROW() can optionally use a third parameter to filter. In this case by Building and Status. It will then return the [Case ID] of the row with the max [CreatedDate] from that filtered list. If the filtered list returns no rows, then MAXROW() will return an empty value.

Ksenia1

That’s what I was looking for! Thank you @WillowMobileSystems for your patience and hanging in there with me!

WillowMobileSys

Yw. And thank you for being patient with me! I am fully aware we took the “scenic” route to get to your answer. It can be frustrating when some of us try to make sure we are giving the best help possible by understanding the use case fully. Sometimes I probably dive deeper into that than needed. Occupational hazard! Good luck with the rest of your app!

Ksenia1

Totally understandable that more details are needed to be able to help out. I am glad you asked those questions and led me to overthink my structure.

Initial value depending on preselection in a related table