Assistance Needed to create relationship

[wperkinson]

 I have three tables whereby I need to create a relationship.  fgs_subscriptionorders, fgs_subscriptionitemsesActive and gap_powerbioptionsetrefsprogram.

fgs_subscriptionitemsesActive are the child records of the parents located in the fgs_subscriptionorders entity.

I need to get a count of all child items for a program.

The program name is stored on the parent record. 

The gap_powerbioptionsetrefsprogram is an entity created to allow me to capture the labels instead of the numberic value of an option set.

For example: program name of: TESTPROGRAM is on four parent records.  Each parent record has 10 children.

I wish to display in a graphical format that we have 40 child records that fall under the program "TESTPROGRAM"

How can I relate these three entities in order to get this done?

As you can see they are related, but I am not sure how to get to the label from the gap_powerbioptionsetrefsProgram from the fgs_subscriptionitemsesActive

Thank you,

Will

[v-yuezhe-msft]

@wperkinson,

Could you please share dummy data of the three tables and post expected result based on the dummy data? Also we need to know what field you use to create relationship among the three tables.

In addition, when you drag program name field of gap_powerbioptionsetrefsprogram and child item field of fgs_subscriptionitemsesActive in a table visual, do you get desired result?

Regards,
Lydia

[wperkinson · Accepted solution]

Thank you for the reply.

I was able to resolve my issue by creating a merge of the two tables in question.  In this way I could create a relationship between the merged tables and the third table in question.

Regards,

Will