Connecting Data

[Sacred_Totems]

Using Canvas & CDS:

I'm making an app that requires 3-5 tiers of questions/input. 

For example, each user will create a building list, then each building has its own specs they must enter in.  The specs will be used to calculate building related output for the user to see later.

I am not sure what to do in order to get my input fields to store everything related to a building easily referenceable so to view it as text after. I imagine I must create a unique hidden identifier somewhere. Other than that I am lost.

[EricRegnier · Accepted solution]

Hi @Sacred_Totems,

I assume you are using CDS to manage your entities, data and forms (model-driven). Based on your diagram, seems you can easily achieve this out-of-the-box (OOB) with 3 entities, namely User, Building and Specification:

User

• Entity already exists OOB. No change required. Every user in your environment that has a license will have a user record.
• More info: https://docs.microsoft.com/en-us/power-platform/admin/create-users-assign-online-security-roles and https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/user-team-entities 

Building

• New entity. Make sure when you create the entity that the Ownership type is "User or team". In short, this will allow an OOB relationship between User and Building, and will help you going forward to be flexible with security requirements.
• Add your required Building fields and update the main form.
• On the form, add a sub-grid of Specification. This will allow users to add/remove/edit specs associated to the building.

Specification

• New entity. Make sure the Ownership type of the entity is also "User or team".
• Add your required Specification fields and update the main form.
• Add a field of data type lookup to Building entity. This will create your 1:N relationship with Building

Besides the entity modeling above, you'll have to create/modify an existing model-drive app if not already done and add these entities to the sitemap. Similarly, you'll have to create/modify security role for your users to access and manage these new entities. 

This should be a start and hope it all makes sense...

[v-bofeng-msft]

Hi @Sacred_Totems ：

Do you want to create a table with a three-level hierarchy?

Although this can be done, it can become troublesome when updating or storing data.

I suggest that you can try this idea: Using a normal table to store the data, and using the constructor function to turn the data source into a three-level hierarchy table when referencing the data.

I’ve made a test for your reference:

My data source :MyTable(collection)

1\Add 5 textinput controls(TextInput1/ TextInput2/ TextInput3/ TextInput4/ TextInput5)

2\Add a button control and set it’s OnSelect property to:

Collect(
    MyTable,
    {
        User: TextInput1.Text,
        BuildingID: TextInput2.Text,
        BuildingName: TextInput3.Text,
        SpecID: TextInput4.Text,
        SpecName: TextInput5.Text
    }
);
Reset(TextInput4); /* After the data is submitted, the input of the spec field is cleared by default*/
Reset(TextInput5);/* After the data is submitted, the input of the spec field is cleared by default*/

3\Add three gallery controls (MYUser/MYBulinding/MYSpec)

MyUser-items:

GroupBy(  /*Group by user name to construct a third layer structure*/
    GroupBy(     /*Group by user name and BuildingID to construct the second layer structure*/
        MyTable,
        "User",
        "BuildingID",
        "BuildingName",
        "myspace"      /* The new group’s name is myspace */
    ),
    "User",
    "Mybuliding"   /* The new group’s name is Mybuliding */
)

MYBulinding-items:

MYUser.Selected.Mybuliding

MYSpec-items:

MYBulinding.Selected.myspace

Best Regards,

Bof

[Sacred_Totems]

@EricRegnier @v-bofeng-msft 

Wow, you are both so thorough with your answers!  Everything you said made perfect sense.  I did find I need to clarify things more though. Please forgive me, this is all still rather new to me.

I have edited my original post to be more precise.

I am using Canvas, and am looking to have up to 5 tiers of entities.  Would also like to be able to make reference to specific entities within tiers independently by filtering.  So for example.  Using the logged user for Tier 1,  then on the first page the user selects the Building (Tier 2),  that selection will trigger a variable/default (I am not exactly sure how I am going to do it yet) which will mean all subsequent pages will make reference to Specs (Tier 3) and Options (Tier 4) based on whatever the variable is currently set at.  

The entity data input/output will not show up all in one form, but rather at different points throughout the app afterwards.

Connecting the Tiers is my first challenge.  Drawing up specific options for drop down menus or output is my second challenge.  

I believe the one-to-many settings will allow for me to do this.  I just have not seen any tutorials or reference on how to do what I am aiming to do yet.

[v-bofeng-msft · Accepted solution]

Hi @Sacred_Totems :

If you need such a multi-level relationship, I suggest you refer to @EricRegnier 's suggestion.

In addition,using the model-drive app as @EricRegnier said is a good way.
If you insist on using the canvas app,I think this link will help you a lot:

Relate and Unrelate functions in Power Apps

Create and edit One-to-many or Many-to-one entity relationships using Power Apps portal

Best Regards,

Bof