Skip to main content

I have an aggregate model that groups Opportunity data by month and year. I’d like to:


  1. Pass the month and year to a drawer to display data from a basic model to display a list of Opportunities by month. The aggregated fields are:

<groupby method="simple">

	<field id="CloseDate" name="cyCloseDate" function="CALENDAR_YEAR"/>

	<field id="CloseDate" name="cmCloseDate" function="CALENDAR_MONTH"/>

</groupby>

But when I try to activate and set model conditions based on these dates, both {{cyCloseDate}} and {{cmCloseDate}} are sent as NaN when I view the condition in the debug console. In the aggregate table output, these render as “6” “2023” if the row is for June 2023. But I cannot seem to get the drawer activate condition right.



  1. What should the context condition be for the drawer table? I can only use a single value for the context, not month + year (related to question #3, is how to add a formula field to an aggregate model)




  2. How do I create a formula field on this model so that I can display a column on the Aggregate table with the data of Month Name rather than than the month? I do not see a grouping formula for month name.



I see others in the community have done this but they are too smart for me and haven’t posted their code examples!


Happy to send samples of the code. This is the opportunity aggregate model:


<model id="Opp_FY" limit="" query="false" createrowifnonefound="false" processonclient="true" datasource="salesforce" type="aggregate" sobject="Opportunity" orderby="CALENDAR_YEAR(CloseDate),CALENDAR_MONTH(CloseDate)">

			<fields>

				<field id="Amount" name="sumAmount" function="SUM"/>

				<field id="ExpectedRevenue" name="ExpectedRevenue" function="SUM"/>

			</fields>

			<conditions>

				<condition type="fieldvalue" value="2024" field="Intended_Fiscal_Year__c" operator="=" mergefield="ProposalIFY" novaluebehavior="noquery" enclosevalueinquotes="true"/>

				<condition type="fieldvalue" value="60" enclosevalueinquotes="false" field="Probability" operator="gte"/>

				<condition type="fieldvalue" value="100" enclosevalueinquotes="false" field="Probability" operator="lt"/>

			</conditions>

			<actions/>

			<groupby method="simple">

				<field id="CloseDate" name="cyCloseDate" function="CALENDAR_YEAR"/>

				<field id="CloseDate" name="cmCloseDate" function="CALENDAR_MONTH"/>

			</groupby>

		</model>

Thank you!


  • Meredith

Just what I needed!! A distraction for my morning. 😃

This topic raises some interesting topics. All shown in the page I’ve attached below.

Topic 1: Reconstructing Dates
Date Grouping aggregations output numbers, and so to use the result of that grouping for other things like Model Conditions or better Labels - you have to reconstruct a date out of the grouping by stitching strings together.

Create a formula with Return Type of Date that looks like this:

JOIN_TEXT("",{{{cyCloseDate}}},"-",{{{cmCloseDate}}},"-01")

This will generate a Date Object that you can use to manipulate with any of the other date formatting or manipulation functions skuid provides. (DATE_ADD, MONTH_NAME_SHORT, etc).
(See Docs here)

Topic 2: Salesforce Date Fields
The strategy above would work to pass Date values into model conditions in most situations, but Salesforce DateField format is not a standard Date Object. (Their DateTime format would work). So passing the result of that Formula above into a “CloseDate” condition will break. It wants the special Salesforce Date object. To get this we have to do more manipulation and output a STRING instead of a date.

This means I can’t simply use DATE_ADD functions to produce an End Date for my Detail query, but instead I have to do some complex manipulation to figure out when the end date should advance years instead of advancing months in my string. See the UI only fields in the Aggregate model of the page below.

Topic 3: Managing Drawer context
Filtering drawer contents so they only show the appropriate data is a complicated thing. It all happens client side - so the data has to be in your model. Fortunately - UI Only formulas are recognized and work in these context conditions. Create FORMAT_DATE formulas on the Detail Model that provide “CloseDate Year” and “CloseDate Month” - and use these to compare with the GROUPING fields in your aggregate model. You can add multiple context conditions.

Topic 4: UI Only Fields in Aggregate models
One final item. Adding UI only fields in Aggregate models happens in the “Aggregations” section. Look for the plus sign.

Ok that’s enough. Below find attached an XML file for a page that does all this work. Let me know if you have further questions.

Rob:


Thank you so much for the detailed response and page example. This answers my questions and more.


I do have an issue however, and that is all of the formula fields aren’t recognizing the aliases, cyCloseDate and cmCloseDate. So the output when I view it in my org looks like this:


AggDateGroups_screenshot

If the screenshot doesn’t display, here are the first few lines:

Aggregate Model






































Row drawer Close Date Sum amount StartDate EndDate MonthName Actions
Yr: 1993 Mo:8 $1,594,200 -0-01 -01-01
Yr: 1991 Mo:2 $2,156,676 -0-01 -01-01
Yr: 1988 Mo:12 $1,064,487 -0-01 -01-01

I have the latest SFX release 16.1.4. I’m a System Admin with the Skuid Admin permission. What could be missing in my set-up that these formulas aren’t working? The only way I’ve been able to make this work is if I add the CloseDate field as a Grouping field to my model (but that means that my table output is wrong) and updating the formulas to use CloseDate, like this:


IF(LEN(MONTH({{closeDate}}))=2,

    JOIN_TEXT("",YEAR({{closeDate}}),"-",MONTH({{closeDate}}),"-01"),

    JOIN_TEXT("",YEAR({{closeDate}}),"-0",MONTH({{closeDate}}),"-01"))

Any ideas as to what could be missing in my org? Please let me know if I should send this to your support team.


I appreciate all of your help!


  • Meredith

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings