Coda & Junction Tables

1

Hello Coda Community,

  • I have three tables: Customers, Items and Orders
  • Customers can order multiple items.
  • I need the order table to list each item ordered by the customer and assign a unique order number to each order.
  • Currently, the order table has lookups to the customers and items tables

In the past I built something crude and similar in Airtable using a ‘customer-item’ junction table.

I’ve been digging around the various Coda resources (help and community) looking for info on junction tables, but I’m not finding a lot of info out there.

This leads me to ask, are Junction Tables used in Coda databases? Or, is there an alternative Coda like way of doing things in these scenarios?

Any tips or links to resources are greatly appreciated.

Thank you.

2

Same here. You’ll have four tables:

  • Customers
  • Items
  • Orders (each order linked to a Customer through a single-select lookup)
  • Order items — a row that links together an Item and an Order through two single-select lookup columns.

Once you have this set up, you can automatically link Order Items back into Orders through a formula, e.g.

[Order items].Filter(CurrentValue.Order = thisRow)

(don’t forget to make it a lookup column on table Order items and select the Multi-select flag)

And by association you can then pull the Items rows from those Order items if you need these.

thisRow.[Order items].Items
3

Thank you Paul. I’ll give this a go.

4

Hello again Coda Community

I’ve had a go following Paul’s tips, I think it’s working, but with my lack of knowledge I’m unable to tell if I’ve done it correctly.

I’ve shared the Doc Copy of Junction Test

  • Are these tables set up correctly for this junction table use case?
  • How do I calculate the ‘Order Total’ in the ‘Orders’ table? (Coda formulas baffle me somewhat :confused: )
  • When this Doc is out in the wild being used, into which table does the user add the order info, Orders or Order-Items?
  • Any tips on making the user experience nice and simple when adding orders?

I apologise if these questions are basic. I’m at the beginning of my Coda journey and getting to grips with the learning curve is quite a challenge.

Thanks again for your help,

5

I thought of this, you chain information

image
image2029×882 86 KB

cheers, Christiaan

1 Like
6

@Andrew_P,

we use junction-tables all the time in coda to deal with many-to-many relationships, like your case with costumers, orders and products.

but coda does not call them ‘junction-tables’. they are just plain ordinary ‘tables’.

they contain the two lookup columns to do the junction job, one to join to the first table (say order) and the other to join to the other table (eg product).

so the solutions given by @Paul_Danyliuk and @Christiaan_Huizer, are examples of using a junction-table. its just not called that in coda.

respect
max

1 Like
7

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