3 Levels Multi-Select Picker

[Amanco]

Hello,

 

I need help please understanding the relationship for making 3 levels multi-select picker from a list. 

Example: I have a list of 10 colors, if first table selects 7 colors, then the second table sees only 7 to pick from and if I pick 3 out of 7, then the 3rd table will see only 3 to pick from.

I have  the following tables

1) Context table (tbl_Context) = Anchor

2) Table with the list to pick from (tbl_List)

3) then I have 3 other tables table A, B, C

my approach (Anchor-Buoy) so far as follows: 

--------------------------------------------------------------------------------

tbl_Context --> tbl_A

tbl_Context --> [Join_table1 with global connecting tbl_A] <-- tbl_List

----------------------------------------------------------------------------------

tbl_Context --> tbl_B

tbl_Context --> [Join_table2 with global connecting tbl_B]  <-- [TO of Join_table1 ]

----------------------------------------------------------------------------------

tbl_Context --> tbl_C

tbl_Context --> [Join_table3 with global connecting tbl_3]  <-- [TO of Join_table2 ]

---------------------------------------------------------------------------------

I am able to pick only from the first level but could not solve the 2nd and 3rd levels, is there another simpler approach since it is only one list to pick up from? with filter/omit values?

 

I really appreciate any guidance.

Thank you

 

 

[comment]

Can you explain the purpose of this exercise? Usually a multi-level selection consists of selecting a single choice at each level - for example, select a continent, then a country in the selected continent, then a city in the selected country. I am struggling to see a practical use for narrowing the choice from 10 to 7, then to 3 as you described.

P.S. Please do not use abstract table/field names. 

 

[Amanco]

5 hours ago, comment said:

Can you explain the purpose of this exercise? Usually a multi-level selection consists of selecting a single choice at each level - for example, select a continent, then a country in the selected continent, then a city in the selected country. I am struggling to see a practical use for narrowing the choice from 10 to 7, then to 3 as you described.

P.S. Please do not use abstract table/field names. 

 

Hi Comment

sorry about the confusion,

The purpose is that I have symptoms and each symptom has a list of diseases.

example:

1) First picker:

each symptom will have many diseases. 

example:

[Abdominal pain] could be a result of ulcer, medication related, bacteria, cancer…etc

 

2) second picker:

Abdominal pain [Location] will narrow the above list of diseases based on the location.

example: for upper central area, i will pick up ulcer and medication related.

 

3) 3rd picker:

Abdominal pain location [medication history] will narrow the list to medication only.

at the end, i would like to use (maybe pattern count) to see how many times each disease was picked and sort by the highest number first.

 

this can be done for each level. At the symptom level (if more than one symptom), at the second level and the 3rd level.

the higher the number, the more chance that this symptom is related to this disease.

 

i hope this makes more sense, please let me know if you have more questions.

 

Thanks 🙂

 

 

[comment]

IIUC, you want to have a list of primary symptoms. Then each primary symptom has a list of secondary symptoms. And each of these a list of tertiary symptoms. 

The crucial question here is how many items will the user be able to select at each level? If it's more than one, it gets complicated - because you will need a sub-table to record each level's selections.

Also, if you want the selections to narrow down the list of possible diseases in the end, you will need to construct an AND relationship - which is also non-trivial.

Finally, there is the question whether there is any interaction between selections; IOW, if the available sub-selections can depend on the combination of selections made in the preceding level. That would make it even more complicated.

You describe your skill level as "novice". I don't think this is a project suitable for a novice - unless there is a single selection at each level,.

 

[Amanco]

2 hours ago, comment said:

The crucial question here is how many items will the user be able to select at each level? If it's more than one, it gets complicated - because you will need a sub-table to record each level's selections

 

Thank you comment.

Yes, more than one item, hence, I have 3 join tables to save each level's selection.

 

2 hours ago, comment said:

Also, if you want the selections to narrow down the list of possible diseases in the end, you will need to construct an AND relationship - which is also non-trivial.

Yes, this is what actually I need guidance with?

how to construct the relationships and at the same time show only the prior selection only. right now, at level 2 for example i am able to show all the selections like level one.

 

2 hours ago, comment said:

Finally, there is the question whether there is any interaction between selections; IOW, if the available sub-selections can depend on the combination of selections made in the preceding level. That would make it even more complicated.

Not really, all what I need just to answer the "questions" and build the list of diseases based on the symptoms questions.

I have the backend between the 3 levels connected and ready, I just need to choose the selections for each.

 

2 hours ago, comment said:

You describe your skill level as "novice". I don't think this is a project suitable for a novice - unless there is a single selection at each level,.

ok, I guess I will change it to intermediate once i am done solving this 😎

 

Thanks again

[comment]

Well, it's an awful amount of work and not something I want to be spending my time on right now.

Off the top of my head, I imagine the core relationships would be something like this:

 

I suppose you will also need a number of auxiliary relationships - for example, you will want to isolate the primary selection for which you are selecting the secondary symptoms, so that only the secondary symptoms related to the specific primary one are shown.

As I mentioned earlier, the relationship between Patients and Diseases (the one shown in red) needs to be an AND relationship. How exactly this needs to be implemented could be a topic for a separate thread. I'll just say that you need to decide what exactly you want. Suppose there are 4 diseases with the following symptoms:

A, B
A, B, C
A, B, D
A, B, C, E

and a patient with symptoms A, B, C. Which of the 4 diseases should be shown in your final list? 

 

6 hours ago, Amanco said:

I guess I will change it to intermediate once i am done solving this 

Perhaps even higher ...

 

 

Edited August 6, 2021 by comment

[Amanco]

Thank you comment, 

I will give it a try and report back.

12 minutes ago, comment said:

As I mentioned earlier, the relationship between Patients and Diseases (the one shown in red) needs to be an AND relationship. How exactly this needs to be implemented could be a topic for a separate thread. I'll just say that you need to decide what exactly you want. Suppose there are 4 diseases with the following symptoms:

A, B
A, B, C
A, B, D
A, B, C, E

and a patient with symptoms A, B, C. Which of the 4 diseases should be shown in your final list? 

Since diseases don't necessarily present with all symptoms together, all the 4 diseases should show but the sequence will be based on the (pattern count) of the symptoms i.e

1)  A, B, C, E and A, B, C

2) A, B, D and A, B

 

1 hour ago, comment said:

Perhaps even higher ...

I will do my best 😁

 

Thanks again for your help

[comment]

If you want to display all diseases that share at least one symptom with the current patient, then you need an OR relationship - which is much simpler that an AND relationship. In fact, you just need to show the diseases that are related through:

Patients -< PrimarySelections  -< SecondarySelections -< TertiarySelections >-  TertiarySymptoms >-< Diseases

But if you want to sort the related diseases by their relevance to the current patient, you will have to find a way to pass the current patient's symptoms to the Diseases table. And you will want to use FilterValues() and ValueCount() rather than PatternCount().

Another simplification you may consider is using checkbox fields in the Patients table to make the selections and eliminate the three join tables.

 

Edited August 6, 2021 by comment

[Amanco]

Dear Comment, 

Finally I managed to connect the relationships, I did it using 3 separate join-tables and using a card for each join-table for each of the 3 pickers.

Now I am faced with the issue on how to filter/limit records in the card list view of the subsequent symptoms i.e:

for Symptom 1: (attached image): since it is the first to be created in the table, the pickers work fine.

but when I move to create the list of symptom 2, the picker will also use the list of diseases in the join-table from the first symptom and so on.

so I need help please on how to filter the list-view in the pickers based on the (Primary_Symptom ID). I am using global fields in the (anchor=patient table) to link the join T.Os.

I thought of 2 methods to filter the picker list based on the global fields using a script with: enter find mode or loop script, but was not successful.

Any guidance please?

Thanks,

 

 

Edited September 2, 2021 by Amanco

[comment]

I am afraid I wasn't able to follow your description. Perhaps this could help you:

If you have a Parent -< Child -< Grandchild relationship, and you do GTRR to Grandchild from the layout of Parent, you will get all Grandchild records related to the current parent, through any of the parent's children.

But if you place a portal to the Child table on the layout of Parent, and do the GTRR from there, you will get only grandchildren related through the child in the active portal row.

 

 

Edited September 3, 2021 by comment

[Amanco]

On 9/3/2021 at 11:57 AM, comment said:

I am afraid I wasn't able to follow your description. Perhaps this could help you:

If you have a Parent -< Child -< Grandchild relationship, and you do GTRR to Grandchild from the layout of Parent, you will get all Grandchild records related to the current parent, through any of the parent's children.

But if you place a portal to the Child table on the layout of Parent, and do the GTRR from there, you will get only grandchildren related through the child in the active portal row.

 

 

Thank you comment, 

(1) I am able to separate the children/grandchildren records, however I can't constrain or show only the records of the selected symptom. 

In the table attached for example: from the first join table where I use the picker/multi-select card based on it.

I get all the records in blue instead of the Red (Chest pain=g_selected question). (I am using look-up field in list view in my card window picker)

 

I was able also to isolate the (Red) records using a " a list function", however I was not able to load them in the picker window successfully.

(2) I am currently using 3 picker windows and each picker comes from a join-table. I have a feeling that it is better to use only the main DISEASES_table as a picker for all, only if I am able to know how to load the active selected records into the picker in a view list. (especially later that I will need to use ValueCount() to know how many times a disease was selected.

please let me know if you need more information.

Thanks

Edited September 7, 2021 by Amanco