Using automation to add invoice # to all rows matching current filters

The flow I’m trying to create is that when I click Create a New Invoice, it adds a row to the Invoices table and adds the date and the Client name that the current list is being filtered by. When it opens I will add the Invoice number and Invoice Description.

When I add the invoice number I want an automation to add the invoice number to the invoice column of all the currently filtered rows.

When I tried copying the Table View Filter and pasting into the automation filter, I got an error.

thisRow.Client.matches([Invoicing Product Client Filter]) AND thisRow.Category.matches([Invoicing Product Category Filter]) AND thisRow.Company.matches([Invoicing Product Company Filter]) AND thisRow.[Area/Room].matches([Invoicing Product Room Filter]) AND thisRow.[Pre-Purchase Status]=“4-Ready To Invoice”

That is working as intended to filter the table view. But when I tried to copy and paste that into the automation filter, I got an error. So then I edited everywhere that it said “ThisRow” and changed it to “Master Product List”

[Master Product List].Client.matches([Invoicing Product Client Filter]) AND [Master Product List].Category.matches([Invoicing Product Category Filter]) AND [Master Product List].Company.matches([Invoicing Product Company Filter]) AND [Master Product List].[Area/Room].matches([Invoicing Product Room Filter]) AND [Master Product List].[Pre-Purchase Status] = “4-Ready To Invoice”

It was no longer throwing an error, but it was saying “=false” instead of displaying the number of matching rows, the way the table view filter does.

When I click test on the automation, it says “No action taken”.

So I’m pretty sure something is wrong with the custom filter.

I managed to get it partly working.

I created a new column in the Master Product List Named “Item ID” and set that as the display value.
I create a checkbox column named “Add To Next Invoice” with this filter:

thisRow.Client.matches([Invoicing Product Client Filter]) AND thisRow.Category.matches([Invoicing Product Category Filter]) AND thisRow.Company.matches([Invoicing Product Company Filter]) AND thisRow.[Area/Room].matches([Invoicing Product Room Filter]) AND thisRow.[Pre-Purchase Status]=“4-Ready To Invoice”

That adds a checkbox only to the items matching the currently filtered table view.

Then I added a lookup column in the Invoices list with this formula:

[Master Product List].filter([Add To Next Invoice]= True)

That successfully pulled in a list of all items being added to this invoice.

So then I added an automation using the same formula to add the new invoice number to all of those items. In the formula results display, it shows the correct selections. But when it runs, it adds the new invoice number to every item on the master product list, not just the 11 from the filter.

Is this a bug?

1 Like

Hi Bianca. I managed to do something similar, but my doc has a somewhat different schema than yours.

I’m using buttons that push other buttons. When you create a button and choose what action you want it to do you can also make it “Push buttons”.

I have these 3 tables:

  • Inventory - has [Item ID] display column, [In Cart] checkbox column, and other columns with info about my items.
  • Invoices - has [Invoice #] display column, various date columns, and lookup column [Client Name]
  • Invoice Items - has [Item ID] lookup display column and [Invoice #] lookup column

My workflow is more like this:

I have a “shopping cart” section that has a filtered view of my Inventory table based on the checkbox column and a button called “Add to New Invoice” that creates a new invoice and adds all the items in my cart to the new invoice by pushing these other buttons in this order:

  1. “Create New Invoice” adds a new row to my invoices table and opens up the row for editing (so it looks like a form). The Invoice # gets automatically generated by a formula based on a few date and client name columns.

  2. “Items to Invoice Items” the action is a formula that filters the [Inventory] table based on the cart items and adds new rows to the [Invoice Items] table. It adds a new row for each of the items in my cart where it associates the Invoice # with the Item ID. I’m certain I had help in getting the formula for this button, but I couldn’t find the source… I suggest you look into the FormulaMap formula. The formula I’m using is something like this:
    Inventory.filter([In Cart]=true).FormulaMap(AddRow([Invoice Items],[Invoice Items].[Item ID],[Item ID],[Invoice Items].[Invoice #],Invoices.Last()))

So you can do all of this by just pressing one button :slight_smile:

In the “Cart” section of my doc I keep the Cart view of my Inventory table and the “Add to New Invoice” button. I keep the other buttons sort-of hidden in the “data” section.