CDS Setup Help

[Bahalzamon]

This is my first real venture into the CDS. I have done the App In A Day and am working on something much larger and coming here for some guidance from more experienced users. 😃

I work for a non-profit and am creating a medical client intake system within a model driven PowerApp.

Currently I am working on the CDS setup and I have run into a couple issues adding lookups and a good use for the Primary Name Columns as apparently different tables with Lookups cannot have the same column names.

So basically I have a form that is something like ~250+ questions (haven't counted, just a glancing guess). My current plan was to break each section (demographics, medical, family, education, etc...) into separate Tables (entities).

But I am used to SQL and somewhat lost with these Primary Name Columns.

Should I just add these hundreds of columns into one Table or break them out? I just feel that many columns may be excessive.

I also have to add A child table and a interaction tracking table.

My thought on the child table would be a lookup to the client, child name, age, etc...

Then the Tracking I am unsure as I have seen what looks like tracking things built into even the app in a day thing that I never added. Where is that data and can I query it?

[DianaBirkelbach]

Hi @Bahalzamon , 

Your experience to SQL gave you the right hint: it would be too many columns.

How about making one table for the questions: the question itself could be the primary name. Each question will be a record in the "questions" table.

Then you could have a second table for answers, with a lookup to the questions ( child table to questions).

For the rest of child tables, I would need more information, in order to see where they belong.

Hope this helps.

Kind regards,

Diana

[Bahalzamon]

@DianaBirkelbach , thank you so much for replying. 😃

I have created a basic diagram of how I am interpreting your reply and I had a couple of follow ups.

• I am unsure what the primary name of the answer table would be and as for the answer how do I accommodate all the different data types?
• As for before the Child table I was referring too was actually Children data lol, sorry I should have explained, i did add it into my basic diagram.

Lastly the "tracking" table I was referring too seems like a Timeline. From looking around it seems I can make custom Timeline options.

https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/set-up-timeline-control

I will have to experiment, but I think I have to make a new table for each type of custom activity I need.

[Fubar]

Primary name is just a text field - treat it as such - however, it is the field that is displayed to users in Lookups (and whilst by default it is mandatory you can set it to be optional - but bear in mind the Lookup dependancy).  What you can do is copy the Question into the name field (not the lookup, but the value) in model driven apps we would do this by making the Name optional and then with a realtime workflow when the record was created/updated copy the Lookup into the Name field (as its simple to do in the workflow).

It depends on what you need, but it can get a bit convoluted, what you do is implement question types (like single line of text, Yes, No etc and use Lookup for dropdowns), so you define a question and what its answer type should be.  You then create a set of Answer records that copy the Question and show/hide fields of the respective types - so the use only sees a Question and an Answer field.  When the user provides the Answer you copy its value into a text field (so in views etc you can show the question and answer provided easily)  - note: there is a lot more complexity behind what I have described and for dropdown type questions you would implement something using related entity filtering (question to answer choices).

[DianaBirkelbach · Accepted solution]

Hi @Bahalzamon , 

I agree with @Fubar in almost all aspects. 

• The primary name of the answer table could be a composition of "question text" and "client name". Setting it to "optional" and filling it using a worflow/flow or cleint script (in the app itself for instance) are a few possibilities.
• I did a few implementations like this. 
  • In the Question you add a "choices" column, for the question type: string/boolean/number/choices
  • In the Answer table you would need to add  separate columns for each data type
  • In the app you hide the columns that are not corresponding to the question type
  • Is exactly what @Fubar described, just that I wouldn't do a text column where the answer text is copied. That's because the data there can't be translated or formatted respecting the user settings (this changes for each user). So maybe instead of that, you can 
    • show all answer columns in the view, or
    • if it's a model-driven app, make a PlugIn on RetrieveMultiple, and fill in the content at runtime (would be a dummy, empty field)
    • if it's a canvas app, you could decide in the Gallery which answer column is shown

Hope this helps!

Kind regards,

Diana