Forum Discussion

EmberKrumwied's avatar
EmberKrumwied
Qrew Captain
8 months ago

Filter records based on table connection

Not sure if the title exactly describes what I am trying to achieve, but here is what I'm working on.

I am trying to create a simple-ish application for one of my teams where they can track projects, enter associated time entries for each and then create invoices for those entries.

My setup is Customers table, Projects table, Time Entries table, and Invoices table.

Customers relates to Projects and Invoices (1 Customer can have many Projects and Invoices).

Projects relates to Time Entries and Invoices (1 Project can have many Time Entries and many Invoices). 

Invoices relates to Time Entries (1 Invoice can have many Time Entries).

What I'd like to have happen is when a user goes to create a new Invoice, they would select a Customer and then a Project and then see a list of Time Entries where Customer and Project match AND the value in the Related Invoice field is blank (i.e. Time Entries that have not been previously invoices).

The thinking is that the user would then be able to select all the Time Entries they wanted to have included on the new Invoice and then the Related Invoice field for those Time Entries would be updated with the newly created Invoice.

Is something like this possible?

Relationships
  • EmberKrumwied's avatar
    EmberKrumwied
    8 months ago

    Never mind...I found a work around. Posting what I did in case it might help others.

    I added a checkbox to the Projects table.  Then created a pipeline to trigger when an Invoice record was updated and the "Create new Invoice" box is checked.

    Step 1 - The pipeline then creates a new Invoice and populates the Related Project ID with the Record ID of the Project.

    Step 2 - Then updates the Project record to remove the check.

    Step 3 - It then searches my Time Entries table looking for any records where the Related Project ID matches AND the Related Invoices field is empty.

    Step 4 - For each it finds, it updates the Related Invoice ID field with the record ID of the newly created Invoice in Step 1.

    Voila, I now have one or more (as it seems to work whether I select 1 or more Projects to create invoices) Invoices created and they are linked to the appropriate Time Entry.  Bonus, if I delete the Related Invoice ID from a Time Entry, it will get picked up on the next round of Invoice creations.

    Not sure if this is the best or most efficient way, but for now it works.

  • EmberKrumwied's avatar
    EmberKrumwied
    Qrew Captain
    8 months ago

    Never mind...I found a work around. Posting what I did in case it might help others.

    I added a checkbox to the Projects table.  Then created a pipeline to trigger when an Invoice record was updated and the "Create new Invoice" box is checked.

    Step 1 - The pipeline then creates a new Invoice and populates the Related Project ID with the Record ID of the Project.

    Step 2 - Then updates the Project record to remove the check.

    Step 3 - It then searches my Time Entries table looking for any records where the Related Project ID matches AND the Related Invoices field is empty.

    Step 4 - For each it finds, it updates the Related Invoice ID field with the record ID of the newly created Invoice in Step 1.

    Voila, I now have one or more (as it seems to work whether I select 1 or more Projects to create invoices) Invoices created and they are linked to the appropriate Time Entry.  Bonus, if I delete the Related Invoice ID from a Time Entry, it will get picked up on the next round of Invoice creations.

    Not sure if this is the best or most efficient way, but for now it works.