Filter by date when use LookupOne

Ask for Help Formulas
1

I’m using LookupOne and trying to filter the data I’m getting back for last month entry. The reference table is monthly invoices and I need to bring columns from the invoice last month to this new table.

Screenshot (48)
Screenshot (48)1920×953 198 KB

2

Hi @miopr.

If I understood your question correctly, you would like to display only the invoices in the Invoices column that are dated within the past month. I can suggest a formula to do that, and display a blank cell if the last invoice is greater than a month old.

Note this formula only works if we have a column that stores just the invoice date (as a Grist Date column), as it makes working with the invoice dates much easier when it’s by itself and not part of any other text. In this case, that column is named InvoiceDate in my formula.

Here is the formula you can try in the Invoice column from the screenshot:

from datetime import date

invoices = list(Facturacion.lookupRecords(Proyecto=$Proyecto))
if len(invoices) == 0:
  return None

last_invoice = max(invoices, key=lambda i: i.InvoiceDate or date.min)

if not last_invoice.InvoiceDate or DATEDIF(last_invoice.InvoiceDate, NOW(), "M") > 0:
  return None

return last_invoice.Proyecto.Name + " / " + str(last_invoice.InvoiceDate)

Breaking the formula down:

  1. We first use lookupRecords (similar to lookupOne, but returns ALL matching records) to get all the records where the invoices are. We wrap this in a list() so we can go through it like a list in the following step.

  2. We want to find the largest (most recent) invoice date in invoices, so we use the max() function to go through the list, looking for the invoice with the most recent date, and using date.min for any invoices which have blank dates (i.e. the oldest date possible for the purposes of finding the max).

  3. Now that we have the last invoice, we have a condition to check that the last invoice does indeed have a date, and that the number of months passed since now is not greater than 1. If either of those is not true, we return None which results in a blank cell value. The DATEDIF function is a useful Grist function that takes a start and end date, and a unit such as days or months, and returns the delta between the dates as a number.

  4. Finally, if we got this far, we have what we are looking for and we simply return a combination of the Proyecto name and the Invoice date with a slash separator as formatted in your screenshot. I assume here that Proyecto.Name is your column name, but you can change this to reflect your own document.

Once you have this working, it’s simple in Grist to add a filter to the Invoices column to hide blank values. You can do this to only see records in your table that have an invoice dated within the past month.

Here’s an example document that should be similar in structure to yours. Since I can’t see the entire structure of your document, there might be some differences but hopefully nothing significant. You can edit it, which will make a copy that you can then save and fiddle around with.

https://docs.getgrist.com/dwr56VC8QruU/Untitled-document/m/fork

Let me know if you have any questions or if you run into any issues with the formula.

George