I have quite a bit of data that needs to be transposed.
I have contact row with lots of columns. For example:
name, gender service1status, service2status, service3status, etc.
I have another table that lists services and I want this list to be dynamic so I can add more services in the future.
I want to combined data between the tables via a relationship so that the service status in the contact is known to the service. That way when using a "list component of services I can still see the individual users status in that service.
The table that lists all the services includes the attribute name that is the column header on my Contact table (service1status). Is there anyway Query or create a dynamic relationship between these two tables so that it will automatically show the right information on service status depending on which contact I choose and which header column I lookup
Let me show example:
Contact 1 from contact table
Name: {name}
Gender: {gender}
Location: {location}
List of Services - Services *list comes from service table, status comes from contact table:
Service 1 = {contact.service1}
Service 2 = {contact.service2}
Service 3 = {contact.service3}
Of note: is that I have over 35 services with more to come so I would prefer to not manually update the screenview for Contact but use a list view of services.
So if I understand your question, you want to create a relation in the Services table that links each service to the contacts that provide that service, correct?
Can you provide a screenshot of your contacts table? It sounds like you have a heading for each service, but what are you putting into columns in each row. Do you have columns values that a relation can use to match up between tables?
1 Like
Does sound to me that the cleanest data structure would have a combination of service x status for each row.
1 Like
Yes let me show you what I mean with a fake example:
Imagine the Contact Table looks like this. The Services Status for each uers are listed in Column C through F
Now here is the Service Table. You can see that there is a lookup value in the Service table that tells what the matching Column should be (see Column D)
So the final results if I were to do it in Google Sheets would be something like this where changing the contact changes the status values, but the services remain the same:
In Glide the final results is more complicated and would look more like this:
For this picture I just created containers with text, but this doesn’t help me solve my bigger problem.
I need to solve the underlying issues of matching columns in Contact to Services table so I can put the Services in as a list with service status coming form the contact table. But then I want to drill down further into each service. There are about 35 total services that we are tracking each with their own subfields to track, and I want to be able to click into services and drill down further into specific data points for each service. All these additional data points are also stored on the contact table in fields that can be looked up via a link between service field and data table. Here is a screenshot of the data dictionary that can link data points to service. These data dictionary fields are also column headers on the contact table.
But I think the same solution will probably help me with both since the main issue is how to do a lookup from one tale to another.
It is worth noting that in my picture above
Your current setup is not very scalable and will require you to make modifications to the app every time you add a new service. You would have to tell glide how to work with each new service column in your contacts table every time a new column is created. Databases work best when the column data structure is stable. Wouldn’t be my first choice for configuring your data structure.
With that said a relation requires matching values in each cell. You March value to value. Not value to heading like you are trying to do.
2 Likes
