A Linchpin's Journey

Let's begin with a simple query. In an opportunity search, one field aggregates Estimated Gross Profit of each line item, while another aggregates the Actual Profit of each line item. Let's label each field {custcol1} and {custcol2}. We set the summary types to Sum. Simple. Now we'd want a Formula (Currency) that returns {custcol2}, but if that has 0 or null values, we return {custcol1}. So... case when nvl({custcol2},0) = 0 then nvl({custcol1},0) else nvl({custcol2},0)) end ...would work, but would likely cause duplicates. Using max on each variable could be a solution to that, but then again, there could be some opportunities with multiple line items. Using max would only get the biggest of those line items, missing out on the smaller lines. A better solution would be using sum distinct instead: case when sum(distinct nvl({custcol2},0)) = 0 then sum(distinct nvl({custcol1},0)) else sum(distinct nvl({custcol2},0)) end In most cases, this would work. But there would be extr...

Okay, so here's an interesting dilemma. A customer created an item saved search using the joined fields Inventory Number/Bin on Hand and, Inventory Number. He wanted to retrieve the Expiration date from the Lot Number Record, but that resulted to each Lot Number Record compare itself to each Bin. This in turn bloated the results from 8 rows to 40. Pulling 3 dimensions to a search, can get messy, since search results are only capable of returning 2 dimensions. Luckily, there's a solution. It seems you have to find fields from each record type that return the same value. This would be your primary key. Then, you use that key as a condition on a case when statement. case when {inventorynumber.inventorynumber} = {inventorynumberbinonhand.inventorynumber} then {inventorynumber.expirationdate} end Also set the Summary Type to Maxmimum for that formula, then Group the rest of the fields. The down side is, since setting summary types forces drill down reports; you can't easily acce...

I've just actually recently got into using the function regexp_substr. It didn't seem very relevant because most SQL functions already capture what customers are looking for. Until now. Someone was looking for a formula that would return either the smallest value, or next smallest, depending on some criteria. While I wasn't able to capture the entire request, I thought of a similar problem: How can I add the amounts of the two lowest SOs per customer? The first thing that came to mind was that I needed to aggregate and rank the SO amounts using listagg. So that's what I did.  listagg({amount}, ' ') within group (order by {amount} asc) Then, I thought of using my recent learnings on regexp_substr. It turns out, that actually did it. These were the formulas: REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)', 1, 1) REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)...

 This search shows a comparison of transaction amounts in YTD vs Rolling Year. YTD case when to_char({trandate},'YYYY') = to_char({today},'YYYY') then {amount} end Rolling Year case when {trandate} >=  to_date('01-'||to_char(add_months({today},-12),'MM-YYYY'),'DD-MM-YYYY')  then {amount} end