Single Value Column Not Populating

Sorry again. Just been really busy with work and other stuff lately. This one needs to deeper focus for me. Still trying to wrap my head around everything. I’m still missing the connection between indication, medication, and color. But let me suggest this…and forgive me if it’s not entirely accurate. What if you change your PEDS Calc Chart table, so everything is in rows instead of multiple columns for each color. So you would have a column for the Indication, a column for Color, a column for medication, and then a final column for the Dosage (like you already have in all of the color columns. Yes, it will translate to a lot more rows, but then you can create template columns in the PEDS table and the PEDS Chart table that join Indication, Color, and Medication together. That way, when a user selects a color, the template column will build with the right information and you can use it to create a relation to the PEDS chart table to find the same rows with the matching template. Then you can display that relation as an inline list.

I think where you are running into trouble (and I think where I get confused trying to understand it), is that you are mixing different types of data in the same table. If you are viewing a particular policy row in the PEDS table, then that is the only row you should be using. The color coded columns should be moved to a separate table since it appears to be a one to many relationship. You are trying to do a one to many relation in one single table, and I think that’s where it gets confusing.

Also, but splitting all of the color data into separate rows, then you are creating a database that is completely dynamic and not reliant on app design and conditionally showing multiple inline lists. Instead, you would end up with a single inline list that’s dynamically populated based on the data that’s returned from the relation. And, if you have a single color column, then your relation can find the correct color, based on the template, and only return those matching rows.

Ultimately, I think you are stuck between saving rows by mixing a bunch of semi-unrelated data in a single table, across multiple columns vs. laying it out so data that is similar is separated in separate tables with proper relations to link everything together. In reality, I think a single color coded dosage column would be much better to work with vs nine.

Honestly, I think if I were to do it, I would kind of tear it apart and start over. First get your Patient Management tables cleaned up with only data that is relevant to the selected policy. You are working with one single policy row, so only the data in that policy row should be what’s seen. Move your color choices used for the dropdown to a separate choices table. Then put your color coded dosage column data in it’s own table and split it apart. Yes, you will have 9 times the number of rows, but it will allow you to use the same relation (instead of a filter) as the source of a single inline list instead of having 9 separate inline lists with conditional visibility.

I think in the long run, by restructuring your data, you will have a much better experience in managing and maintaining your app. It might be more work to rebuild things, but it would be worth it when you have to make modifications in the future. I would have changed a lot more in the copy that I have, but I didn’t want to veer too far from what you already have done…but I think at this point, a restructure would be better. What you have would probably still work, but as you’ve seen, maintenance becomes a lot of work.

2 Likes

Hey Jeff!

I can’t thank you enough for all of your help and guidance with this :pray: It took me a few days but think I got it: https://winged-laborer-7833.glideapp.io/

Will you take a look when you get a minute and let me know if this is how you would do it?

Side note: I’m still putting it all together on the front end so you might see some duplicate columns in the PEDS tables. Once the UI is built, I’m going to go back through and delete the unused ones

Looking at this, I would still do some things differently:

  • You had a broken relation in your Policy Choice Selection Table. This should be linking the selected policy to the policy in the PEDS table.
    image
  • Your Color Lookup in the Choice Selection should be pulling the color from that related row. You have a general lookup to the whole table, which will definitely bite you if you ever end up with a color selected in two or more policies. Never rely on values always being cleared. An app could restart or be closed without a person hitting the floating back button to clear values. Then they will be reverted to the policy selection tab, which you are hiding with CSS, but only if they are on another tab. Trust me…it will happen, and you want to be prepared for those what if situations. Your color lookup in the choice table was pulling back an array and being pushed to the PEDS table as an array, which makes it a lot harder to work with in this case. It should always be a single value, determined by using a non-multiple relation.
    image
  • All of your other lookup columns in the choices table are unlinked, so I’m not sure where you were going there.
  • What I imagined next is something like this template column that joins the single value color choice and medication together. This would be done in both the PEDS table the the Calc table.
  • Once your templates are set up, then you can create a relation in the PEDS table to link that template to the template in the Calc table. I would assume that since you have multiple indications and medications, that you would have to set up multiple templates and multiple relations, however, the number of templates and relations should match the total number number of indications I assume.
    image
  • Finally all of your inline lists would just use the respective relation for each indication. You should never need to have any filters or visibility conditions on your inline lists. They should only show or hide based on what the relation returns. I noticed you had a bunch of inline lists named Grey, and based on your post in another thread, I fear that you are attempting to create several duplicated lists for each color. Don’t do that. The relation will take care of what color and medication dosage is shown in the list. If the relation doesn’t return any matches, then the list won’t show.

With all of the columns you have in there, it’s hard for me to determine what is valid or not. It’s also hard for me to come up with a complete working sample, because it’s a bit over my head and it appears to be a very small sample of data that you have in the tables, so I’m not sure if there really is a policy I can select that will show me all of the medications. But from what I’m seeing with your Calc table, I think you are on the right track. Also, not quite sure where you are going with all of the indication columns in the calc table. Regardless, here is my copy of the app with the changes I screenshotted above. Hope that helps.

5 Likes

Thanks again Jeff!! You got me all squared away :sunglasses:

1 Like

Glad I could help!

2 Likes

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.