Add Rows to an Invoice Automatically

Hey Gliders

Need a little bit of help getting something up and running and keep hitting dead ends.

(Using Glide Tables Only)

Structure
Bookings are linked to clients.
Clients have invoices.
Each invoice is either draft, sent or paid.

Desired Functionality

  • We have bookings, invoices, and clients.
  • When a booking is finalised (done through an action which updated Finalised boolean column) it should check to see if client has a draft invoice already. If so, update a field on the Booking (Invoice ID) which links it to the Invoice.
  • If a client does not have a draft invoice already, it should create the invoice, and then link the booking as above.

I can’t figure out a way to do this. Any help is appreciated.

You didn’t explicitly say so, but I assume that you have an Invoices table that has a BookingID column. If yes, then:

  • In your Bookings table, create a relation that matches the BookingID with the BookingID in your Invoices table.
  • Modify your Finish action to that it checks the status of that relation:
    – If it’s empty, create a new Invoice
    – Otherwise update the existing invoice

Would this allow for the fact that multiple bookings can be on a single invoice? Currently migrating from AirTable to Glide Tables and trying to work my head around the new paradigm

Yes, that should work.

Appreciate the help so far. Just struggling a bit with implementation.

Here’s my understanding of your solution:

Booking belongs to single invoice.
Invoice can have multiple bookings.

Invoice has:

  • Invoice ID
  • Status (Draft, Sent, Paid)
  • Booking ID Column (Text)

(I assume it’ll also need a relation column in order to pick up all bookings that have matching Invoice ID?)

Booking has:

  • Booking ID
  • Invoice ID
  • Relation that matches Booking ID (Bookings) to Booking ID (Invoice)

Finalise Action:

  • When booking is finalised check the relation on the Booking to see if there is anything in it. If not, create an invoice and then update the invoice ID on the Booking. If an invoice does exist there, update the Invoice ID on the Booking with that ID.

Where I’m Struggling:

  • This doesn’t distinguish between whether an invoice is draft or not to link
  • Booking ID (on Invoice) can’t have multiple Booking IDs in or it breaks

What is the significance of the invoice status here?
That is, if it’s not draft, would it be ignored, or…?
What you could do is create an if-then-else column that returns the BookingID if the status is Draft, and then use that column to form the relation from the Bookings table. Then the relation would only consider Draft invoices. But I’m not sure if that gets you where you need to be? :man_shrugging:

Breaks in what sense?
Could this not be a joined list (comma-separated) of BookingIDs?
If you need to use it in a relation, you can convert it to an array with a Split Text column, and then use that to form a relation.

1 Like