Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All, I wouldn't post here unless I were at my wit's end (which I absolutely am! I've spent hours on this!). I would greatly appreciate any help.
I am trying to output the record names of Salesforce Opportunities in a straight table as hyperlinks to the records. For those of who don't know, Salesforce hyperlinks are pretty straightforward: "https://<SF instance>.salesforce.com/<record Id>"
So my end goal here is to get 1 Opportunity name per row in the table, and the output link structured as "https://na4.salesforce.com/<OpportunityId>"
My data model incorporates Opportunity Line Items such that a single Opportunity can have multiple line items. I have a number of expressions in my table that use the following set analysis to limit and sum results based upon a single Opportunity:
=Sum(
Aggr(
Only(
{$<
SFOpportunity_RecordTypeName-={"*Renewal*"},
SFOpportunity_IsClosed={'false'}
>}
SFOpportunity_Amount
), SFOpportunity_Id
)
)
This works just fine, as expected.
I create another expression with a display option of Link. The set analysis I use is completely disregarded. The resulting table has tons of records in it that don't meet the criteria (like where RecordTypeName DOES NOT CONTAIN Standard, and IsClosed = True). The rows that do meet the criteria are properly hyperlinked and take a user to the correct Salesforce record. The rows that DON'T meet the criteria have blank field values, and all of the other expression columns return results of 0. Can anyone help me pinpoint why my set analysis just isn't taking?
=Concat(
Aggr(
Only(
{$<
SFOpportunity_RecordTypeName={"*Standard*"},
SFOpportunity_IsClosed={'false'}
>}
SFOpportunity_Name
), SFOpportunity_Id
)
)
&'<url>https://na4.salesforce.com/'&
Concat(
Aggr(
Only(
{$<
SFOpportunity_RecordTypeName={"*Standard*"},
SFOpportunity_IsClosed={'false'}
>}
SFOpportunity_Id
), SFOpportunity_Id
)
)
Gysbert Wassenaar, this ended up working:
=MaxString(
Aggr(
Only(
{$<
SFOpportunity_RecordTypeName={"*Standard*"},
SFOpportunity_IsClosed={'false'}
>}
SFOpportunity_Name&'<url>https://na4.salesforce.com/'&SFOpportunity_Id
), SFOpportunity_Id
)
)
Maybe like this:
concat(distinct $< SFOpportunity_RecordTypeName={"*Standard*"},
SFOpportunity_IsClosed={'false'} >} SFOpportunity_Id)
Gysbert Wassenaar, thank you for the suggestion. I gave it a shot, but I still get the same flawed results.
Perhaps the following will help determine the issue here. This expression gives me the wrong results:
=Concat(
Aggr(
Only(
{$<
SFOpportunity_RecordTypeName={"*Standard*"},
SFOpportunity_IsClosed={'false'}
>}
SFOpportunity_Name
), SFOpportunity_Id
)
)
However, if I substitute COUNT for CONCAT, the expression returns the correct value per row (1) and the entire table's contents are properly filtered. Am I misusing the CONCAT function?
Gysbert Wassenaar, this ended up working:
=MaxString(
Aggr(
Only(
{$<
SFOpportunity_RecordTypeName={"*Standard*"},
SFOpportunity_IsClosed={'false'}
>}
SFOpportunity_Name&'<url>https://na4.salesforce.com/'&SFOpportunity_Id
), SFOpportunity_Id
)
)