How to get the most recent date as a sub-query

API & Programming
1

I have an “Accounting” app that has two types: Client and Invoice. There is a relation where a client can have many invoices.

Lets pretend the Client type only has one field: Name

The Invoice type has two important fields: Invoice Period (a date field that includes start & end dates) and Date Invoiced (a normal date field).

What I want to do is an API query that returns the Name of each client and their most recent Date Invoiced.

EDIT: This is what I have so far but I think that by using "q/limit": 1 I am only getting a single Date Invoiced value, where as I expected it to work more like a sub-query. So that’s actually not going to work.

"query": {
	"q/from": "Accounting/Client",
	"q/select": [
		"Accounting/Name",
		{
			"Accounting/Invoices": {
				"q/select": ["Accounting/Date Invoiced"],
				"q/order-by": [
					[["Accounting/Date Invoiced"], "q/desc"]
				],
				"q/limit": 1
			}
		}
	],
	"q/limit": "q/no-limit"
}
2

Have you considered adding a formula field in Fibery to calculate the date of the most recent invoice, and then just using the API to retrieve that field?

3

Hello. Unfortunately api does not support other q/limit, than “q/no-limit” for subqueries at the moment. There is a bug on our side, that we does not throw meaningful exception in your case.

Unfortunately even @Chr1sG workaround is not possible for now, as you cannot create such formula (we are implementing such capabilities right now, so it will be released more or less soon).

Can only suggest to stick with “q/no-limit” for now and take first item on your client side, sorry.

1 Like
4

Isn’t a formula field Invoices.max([Date invoiced]) for the Client type all that Dimitri needs?
And then use the API query to get the value of this field for each Client?

Or did I misunderstand the problem?

2 Likes
5

Actually if you only need Date w/o any other fields you may try aggregate function q/max. See example bellow

"query": {
	"q/from": "Accounting/Client",
	"q/select": {
		"Accounting/Name": "Accounting/Name",
		"most-recent-invoice-date": ["q/max", ["Accounting/Invoices", "Accounting/Date Invoiced"]]
	},
	"q/limit": "q/no-limit"
}
2 Likes
6

@Chr1sG No you are correct. I basically ended up creating helper fields with Invoices.Max([Date Invoiced]) and Invoices.Max([Invoice Period].End). I guess the point of this post was to see if it could be done purely in the API.

@Viktar_Zhuk Nice, that works pretty well for simple dates. However it looks like this approach doesn’t work with date range type dates as I get an error: ERROR: function max(daterange) does not exist when trying to do "q/max", ["Accounting/Invoices", "Accounting/Invoice Period"] @Sergey_Truhtanov Any ongoing plans to add max()/min() support for date ranges?

2 Likes
7

Sorry for late response.
Unfortunately it is not possible via api at the moment. There are “q/end”, “q/start” functions in api that you can use to extract parts from date-range, but you cannot use them with max for nested collections. Cannot promise any eta right now.