Good morning, all.
I have been going around in circles trying to get the right syntax on this one.
I have a Dataverse Table (Monthly Quota Usage) with the following Fields:
Environment Name (Text)
Capacity Type (Text, "Log", "File" or "Database", note this is indeed Text and not choice.
Actual Usage (Decimal, this holds the usage for each Capacity type, ie the table has multiple rows for an environment, one row will have the 'Capacity Type' as Log, the next File etc, and the Actual Usage on that row corresponds)
Month Ending (Date only)
My dropdown is called Environment_DD and has the values of the Environment names.
I want to populate the gallery so that firstly looks at the Month Ending dates, subtracts 1 month and converts them to mmm yyyy then group them. Ie this filter should control how many rows we would see in the gallery. And they need to order from oldest to newest.
I create a collection with the OnChange Property of the Environment_DD using the below:
ClearCollect(
colGroupedData,
GroupBy(
'Monthly Environment Quota Usages',
'Environment Name',
'Month Ending',
'Capacity Type',
'Actual Consumption'
)
)
It looks like the Actual Consumption field in my collection isn't getting populated so there is clearly an error above. I guess I need to specify Log, File or Database type? Additionally, I should only collect data matching the Envrionment_DD.Selected.Value = Environment Name. (I had that in at some point, but as I mentioned, playing the circle game).
They gallery items property is:
SortByColumns(
Distinct(
Filter('Monthly Environment Quota Usages', 'Environment Name' = Environment_DD.Selected.Value),
'Month Ending'
),
"Value",
SortOrder.Descending
)
I have added in more text fields in the gallery view as required so it shows as
Environment Name Month Ending Log File Usage File Usage Database Usage Total Usage
If there are no more dates, that should be it.
I get the total usage by adding the textbox names of Log File Usage, File Usage and Database Usage together. (Which I could probably do before hand in the collection, but this at least seems to work).
I have my date in the gallery formatted with:
Text(DateAdd(First('Monthly Environment Quota Usages').'Month Ending', -1, TimeUnit.Months), "mmm yyyy")
I know this is grabbing it from the actual table and not collection, but I could not get the syntax to work with the collection.
Ie triedText(DateAdd(First('ThisItem.'Month Ending', -1, TimeUnit.Months), "mmm yyyy") etc.
Again I think this needs to be done in the initial collection / filter and I should be able to ThisItem.'Month Ending'?
Sorry, there's a slot there.
Greatly appreciate any assistance 🙂
Thank you
RD
