Aggregate of selected records in another db

Panorama X
1

I am attempting to acquire the aggregate sales for a specific SKU for a given time period in another db.

In the Purchasing db, each record represents a single SKU. The History db has multiple records with each record having a specific SKU and quantity for that sale.

In the Purchasing db, in a Text Display object, I am using the following formula.

Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-91) AND «Sale Date» ≤ (today())},"Steinbart Sales History")

The last option of the Aggregate function is ‘Selected records’ or ‘All records’. Given that I only want those records that pertain to the specific SKU that I am viewing in the Purchasing db, it appears that I have to select the corresponding SKU group of records in the History file each time that I change the current record in Purchasing. This does not sound very efficient.

What it appears that I need is for that last option to instead allow me to specify which records should be used. Alas, it does not do this.

Other ideas?

2

It would appear that you need to expand your formula to include a reference to the targeted SKU in addition to the date range. And you probably do want all records evaluated unless you’ve pre-selected a set hat contains them.

3

Ya I went down that route to to attempt to include in the formula a reference to the desired sku but I failed to come up with an effective way to pass the desired SKU into the formula as the QueryFormula is evaluated in the History db. What am I missing?

4

Try using the fieldvalue( function which allows you to set the database it is used in.

5

The documentation explicitly covers this exact situation.

CleanShot 2024-10-22 at 15.42.32@2x
CleanShot 2024-10-22 at 15.42.32@2x2424×1480 528 KB

6

I do believe that I am getting closer given Gary & Jim’s direction but still…

This compiles and gives me a result…

Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-365) AND «Sale Date» < (today()-273)},"Steinbart Sales History",true())

while this does not compile…

Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-365) AND «Sale Date» < (today()-273) AND «Product Sku»=««FHS SKU»»},"Steinbart Sales History",true())

Instead I get this error: “Expression contains an operand when an operator was expected.”

The «FHS SKU» field is in the Purchasing db, while the «Product Sku» field is in the History db.

Perhaps I should take a break and step away? lol

7

I am not seeing the cause of the error. Anyone?

8

I am wondering: You need a pair of chevrons for a field name containing a space. You need double chevrons for the field in the current database. Would you need triple chevrons if the field name (in the current database) contains a space?
Maybe you should avoid the space in the field name and use e.g. “FHS_SKU”?

9

While I agree that having spaces is a field name is never a good idea, changing this did not alter the failure. I do appreciate your outside the box thinking though as I had not tried that.

The error tells me that this is more of a compile error than an evaluation error.