Hello community, in one of my apps i have a ...

[Bernhard_Kurz]

Hello community,

in one of my apps i have a pricelist with prices for each client and product. when i add a new price, i also put in a date from where the new price is available.

so over the time the pricelist includes e.g.: product1 | dateFrom1 | price1 product2 | dateFrom1 | price1 product2 | dateFrom2 | price2 …

now i want to create a pricelist via Workflow rule where i want to show all products with last valid price

eg: product1 | dateFrom1 | price1 product2 | dateFrom2 | price2 …

in google docs template my START Expression <Start:RefColumnOfClientPrices> of course show all the entries for specific client. what would the select statement look like to limit the output to newest price for each product of specific client?

any help would be appreciated

[Fernando_Lopez1]

Try this: Create 2 Virtual Columns in the Product table using the MAX function to get the last price and do the same for the date, then in your workflow use the Product table as source and display the Virtual columns you just created as Price and Date.

[Aleksi_Alkio]

If you create a suitable MAX expression for the Date virtual column as @Fernando_Lopez described, you could use something like…

<<Start: SELECT([RefColumnOfClientPrices][KeyColumn],[Date]=[MaxVirtualDate])>>

[Bernhard_Kurz]

Hello, thank you for the suggestion, but isn’t there a way without virtual column?

eg: is it possible to place OrderBy() in Select() like: ORDERBY([RefColumnOfClientPrices],[Date],TRUE)

does return the list with desc date, if i could put this list in a Select Distinct, it should return distinct product so only the actual price?

<<Start: SELECT(ORDERBY([RefColumnOfClientPrices],[Date],TRUE)[Product], “noWhereClause???”, TRUE)>>

I know, the result of ORDERBY is not a list of REF, but is there a way like this?

thank you in advance

[Aleksi_Alkio]

I’m afraid you can’t write it like that.