Duplicate page - 1 connected view but 1 with duplicated data

1

Hi all,

Sorry, Coda newbie here. Not afraid of using formulas etc if needed.

I’m a coach and am trying to create a Coda document that lists my appointments, with additional pages (one per client) showing a view of those appointments that is filtered to that particular client.

Each Client Page also includes a table showing my assessment notes.

  • Master Page: A Master Table with one row per appointment (already synced from my scheduler app)

  • Client Page: On top, a view of the Master Table filtered to list one client’s appointments. Underneath, an Assessment Table with notes I make during their initial assessment.

I want to create a Client Page ‘template’ (or whatever hack works) that lets me create a new Client page quickly and easily. So far I’ve tried Duplicating.

Duplicating works well for the Appointments table: when duplicating, I select to keep all views connected so everything is synced and I only need to change the filter to show the right client’s appointments.

However, the Assessment Table is different for each client, so I don’t want that table to be connected.

When duplicating my template Client Page, Coda forces me to either connect every view, or duplicate the data without changing other views. It doesn’t appear that I can choose which views are connected and which are not.

I guess the most simple solution would be to copy & paste a blank Assessment Table into every new Client Page. But ideally I’d like to avoid that manual step.

Finally … In the Client Page the filtered Appointments table also contains a Lookup field that links to the Assessment table below it. If I can keep that linked without manually doing it each time, that would be perfect.

Is there any workaround here?

Many thanks indeed!

2

Is it possible to add a (hidden) column to the Assessment table that relates each entry to a client such that you can filter that table as well? This would provide a “blank” assessment table that is still connected to the aggregate data

1 Like
3

Actually, if I may ask: If you don’t want the Assessment table to be connected, why don’t you just create a new table for it instead of using view? Because table views are essentially the same table with the same data. Just shown in different instance.

About lookup field, indeed you can’t use it anymore to pull data from different tables that are dynamically created when you duplicate the page. And the workaround I can think of (as I also happened to stumble on quite similar scenario), is to create a button with modifyrow() formula on the Assessment table that will ‘push’ or send/update the values into the Appointment column intended, instead of using lookup for the Appointment table to pull data from Assessment table.

4

Big thanks to @Billy_Jackson and @Sathya_Thunger for your help.

Regarding your suggestion, @Billy_Jackson: the issue is that the Assessment table lists my notes for that client on over 40 topics. Because there are so many topics, the table is set up so that it’s one row per topic (see below). I have 4 columns of data per topic.

I could in theory switch it to columns instead, i.e. enter all assessment data for each client in a single row … but it would be really difficult to get a visual overview of the assessment: I’d be scrolling sideways across 100+ columns (Topic 1 / Topic 1 Note A / Topic 1 Note B / Topic 2 / Topic 2 Note A / etc).

Unless there’s some way I can create a view of this megatable which shows the data in a nicer way, e.g. stacked vertically like it currently is?

This might still create a problem for my lookup field though (again, see below) – it would probably be really complex or even impossible.

@Sathya_Thunger, that’s also a great suggestion, I guess it leads me to two questions:

  • This way, would I need to manually paste in a blank Assessment table each time I duplicate the page?

  • Regarding the lookup field, I’m not sure how this would work for my case. Basically the rows & columns in my assessment table look like this:

Topic 1 / Select list field / Note A / Note B / Note C
Topic 2 / Select list field / Note A / Note B / Note C
...
Topic 47 / Select list field / Note A / Note B / Note C

I fill in this table in the first session. Then I want to refer to it when I fill in the Appointments table each week, which looks something like this:

Session Date / Topics Covered / Notes

I’d like Topics Covered to “pull” data in from the Topic column in the Assessment table.

e.g. on Tuesday, we covered Topic 1, Topic 14, and Topic 26.

Because I have one single client in my test coda doc, this works really nicely: in my Appointments table, I can hover my cursor over the name of a topic we’ve covered, and see the Assessment notes I had originally made for that client on that particular topic, without needing to scroll down to the Assessments table to see it.

I guess this wouldn’t work if i was instead ‘pushing’ data from the Assessments table.

So it works well for one client … But as soon as I try doing this with multiple clients, it will just overwrite the previous client’s data, because the Assessment table is a linked view.

I guess the simplest thing to do would be to keep it as it is, and when I duplicate a page I’ll need to do these things manually:

  1. Copy & paste a blank Assessment table, then…
  2. Add a lookup field in Appointments table that links to it
  3. Hide the lookup field from the previous page I duplicated

But that’s a little clunky.

Sorry for the essay but thanks in advance for any tips, however brief.

5

When you say the table is different, what do you mean with that? Does each client have different columns?

6

Sorry I misunderstood the first time.
So what you want is: whenever you duplicate a new Client Page, Appointment table stay synced, and Assessment table is independent.

Ok from my perspective, maybe you can remove the Appointment table view from Client Page, and leave just Assessment table with the lookup column pointing to the Appointment table. That way, when you duplicate the Client page, you can select ‘duplicate data’ instead of ‘connected views’, and the new Assessment table will still pointing to the same Appointment table as master table.
I mean why would you need Appointment table view in every Client’s page? isn’t it render the main Master page to be useless? (sorry I don’t get the rationale behind the decision because I don’t understand the data involved).

I also don’t understand the importance of Lookup field in Appointment table that link back to each Assessment table.

Does the Display column of Appointment table contains names of each Client? (this is important to determine the best data structure for your case).
Also what is the Display column of the Assessment page? Is it the Topic column?

7

Hi guys,

To answer hopefully both of your questions, here’s my layout:

Appointment table looks like this:

image
image1944×598 85.3 KB

This display column is a hidden field that concatenates First & Last name. I have it like this so that I can see the client’s name in a calendar view of upcoming sessions.

As you can see above, usefully I can hover over the ‘SCHWA’ topic to view the three notes I made about that client’s habits for the SCHWA topic (in the client’s Assessment table).

Assessment table display column = Topic name

image
image1518×204 21.7 KB

The Assessment table lists topics in rows (over 40 of them). For every client, the topic names are always the same and in the same order. During the assessment, I simply fill in the Mistake & Note columns for each client.

I have a Mistakes field for a generic note of where’s they’re generally going wrong (the example above has just one - ‘phonetic pronunciation’, but sometimes there’s more than 1 mistake I add here), then I add extra detail in the 3 Note columns. I have multiple Note columns so I can view them separately.

I’m not especially tied to the Assessment table looking like that. I simply want to be able to enter assessment data and have it clearly viewable & editable. And maybe even pull the Topics into my Appointments table too as I’ve shown above.

I hope this helps clarify?

8

The next problem I’m running into is how to take the data from the Appointments table (i.e. many appointments, one client) and display it in a new Clients table (i.e. one client entry, and a linked field column that shows all their appointments).

I’d like to use this Client table to include more data on them, e.g. how many sessions they have paid for.

That way I can (hopefully) count the number of Appointments a client has already had (data from Appointments table) vs. how many they’ve paid for (data from Clients table).

Not sure how to construct the initial Clients table though – so far I can’t get multiple appointments to combine into one Client entry.

9

So, from what I understand, the Assessment table is to record the Topics.
But what’s the purpose of Appointment table? Is it to record the appointment sessions, or the clients?

If it is recording everything related to the sessions, then I think what you really need is 1 more master table dedicated to your clients (let’s call this Client Table), and have its columns to represent Topics in Assessment table (since you said that the Topics for each client are the same).

Then, all you need is one Assessment table in one Client page (instead of multiple pages for each client, which will end up very confusing when things grow), and then put a control select box (/control > Select) that is pointing to the Client Table on top of the Assessment table, and have the each column in Assessment table to lookup the data from the control box.

This way you can just call your client name using control box, and data in Assessment table will changed according to the client’s. Or you can just leave the Assessment table as selectable list instead, and just fill everything in your Client table.

10

Hmm. I think this is likely the wrong use of columns, which should generally represent discreet TYPES of data, rather than separate entries of similarly typed data - which should all be rows.

Might I mirror what @Sathya_Thunger says and suggest the following Tables:

  1. Topics
  2. Clients
  3. Assessments - with columns Topic, Client, Note and Date

In this way, you can call up a table view of ASSESSMENTS filtered by Client and Date to input new Rows of Topic-Notes for each topic you address. You can then use a separate view of Assessments sorted by date to see how the topic-notes have changed over time.

Alternately, if there are a fixed number of topics, you can make a column for each topic and then see everything in the Detail view in a nice layout you make for yourself. But depending on how many topics you address per session you may end up with a lot of empty rows (if it’s close to 100% of them, I suggest this method. If it’s less than 40%, I suggest the previous)

1 Like
11

Hi guys,

I think I’m getting there, thanks again for your continued help.

The Detail view works very well, so I’ve now switched the Assessments table to have Topics as columns not rows. This is now called my Clients table. I don’t think I need an Assessments table at all, as I only need data on the one single initial assessment I do with them.

Along with Topic-notes, I’ve used the Clients table to add more detail on each client too, which I’m displaying in other views, text formulas, progress bars on the same page.

The next question is, is it possible to have one select control that filters every formula/view/etc on the page to show only the data for that client? Coda’s tutorial on controls doesn’t seem to cover this.

Secondly, how to get the Appointments & Clients tables to sync with each other as soon as one is edited by me or Zapier?

Basically I need an Appointments column in my Clients table to list every appointment associated with a client. Right now my Appointments table gets filled via Zapier when a client books a session with my scheduler. So none of that data syncs into my Clients table yet. I’m not sure how to cleanly get data into both tables, and have those tables link with each other, when an appointment is made/cancelled in the scheduler with a new/existing client.

The only zap I have now is one that adds an Appointments row when a new appointment is booked. I know I still need to add a ‘appointment cancelled’ Zap, but do I need more zaps to get data to both tables? Or should I do it all in Coda?

Any guidance much appreciated!

12

Hi all,

I’m aware I may be testing the patience of people in this group with my mega long questions!

Still struggling with the two questions and wondering if anyone could at least point me to a resource for my further research:

  • How to get one single Select Control element to filter multiple tables/views/formulas

  • How to sync two tables using Zapier/Coda as described below

Thanks so much for any guidance, however brief!

:slightly_smiling_face:

13

Sorry just saw your reply.

So to have a table filtered to certain client defined by the Select control:

  • First of course set the Select control to lookup from the Client table
  • Name/label the Select control
  • Click Filter menu (beside Sort menu) of the table you want to filter, and set it to filter the client column to match the value in Select control.
    Using filter formula: [tablename].filter([clientcolumn]=[selectcontrolname])

About the data flow, from what you described, so essentially the appointment of specific client set by the external scheduler. So in this case, the Client column in the Appointment table is the master data, and we want ‘appointment column’ in Client table to reflect that.

  • First create the column with the lookup pointing back to Appointment table.
  • Using formula, you can set: [appointmenttable].filter([appointmenttable.Clientcolumn]=[thisrow.clientname])

Hope this help

14

Hi Sathya, not at all, thank you for replying.

I’ve managed to get the select control working, although with more simple formulas, e.g. thisRow.Email=[Client Selector] and thisRow.Client .Matches([Client Selector]). Not sure what the difference between those is but both appear to work well.

For some reason your suggestion of a formula threw up ‘circular reference’ error so I couldn’t use it.

Regarding your second point, I’m having trouble with this:

I’ve added a Lookup column in the Client table and then added the below formula based on yours above (I use Email not Client name in case I have two clients with the same name):

[Acuity Sessions (all)].filter([Acuity Sessions (all)].Email=thisRow.Client)

But the result is just [ ] for all rows in the Client table.

I added a new test appointment in Acuity and it goes into my Acuity Sessions (all) table (my appointments table) but nothing updates in the lookup column of my Client table…

Both the Email column in my appointments table and the Client column in my client table display email addresses that match. They are single select fields with multiple selection turned off.

Thank you!

15

oh maybe instead of using = formula, you can try contains() formula:
[appointmenttable].filter([appointmenttable.Clientcolumn].contains([thisrow.clientname]))

Again it is hard to suggest a formula because I don’t know your table structure, column, etc. But any will do as long as it works.

About the result, maybe because we’re trying to match different data type. Usually I just solved it by adding .totext for example:
[appointmenttable.Clientcolumn].totext
[thisrow.clientname].totext

16

Thanks, @Sathya_Thunger, I couldn’t get those to work but the winning formula was:

Lookup([AppointmentTable],[AppointmentTable].Email , thisRow.Client).Date.BulletedList()

1 Like
17

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