Can we group in report designer using the Ref Number (custom column) as BOQ1 Prelim as total of 9 (order qty) and BOQ2-MAIN BUILDING as 4 ?
Answer
Report Design Grouping
Best answer by darylbowman
Possibly.
A gnarly formula like this MIGHT do the trick:
=Floor(CDec(IIf(InStr([Table.Field],'.')>0 And InStr([Table.Field],'.')<Len([Table.Field]),Concat(Substring([Table.Field],0,InStr([Table.Field],'.')+1),Replace(Substring([Table.Field],InStr([Table.Field],'.')+1,Len([Table.Field])-(InStr([Table.Field],'.')+1)))),[Table.Field])))
You have to replace [Table.Field] with your actual field (maybe use find and replace in a document editor) and it must be inside brackets.
The idea is that it will remove any instance of . except the first one, convert the result to a decimal, and use Floor() to get the smallest integer value. Assuming this works, it MAY actually work to group it.
+15Possibly.
A gnarly formula like this MIGHT do the trick:
=Floor(CDec(IIf(InStr([Table.Field],'.')>0 And InStr([Table.Field],'.')<Len([Table.Field]),Concat(Substring([Table.Field],0,InStr([Table.Field],'.')+1),Replace(Substring([Table.Field],InStr([Table.Field],'.')+1,Len([Table.Field])-(InStr([Table.Field],'.')+1)))),[Table.Field])))
You have to replace [Table.Field] with your actual field (maybe use find and replace in a document editor) and it must be inside brackets.
The idea is that it will remove any instance of . except the first one, convert the result to a decimal, and use Floor() to get the smallest integer value. Assuming this works, it MAY actually work to group it.
Daryl⚡Freelance Acumatica Developer (MVPx2🏆) for hire ⏩ linkedin.com/in/darylbowman
+3I love your solution
Is the Floor call strictly necessary? Conceptually your construction here looks a bit bigger than I’d expect.
=IF(IFERROR(FIND(".",B1),0)>=1,LEFT(B1,FIND(".",B1)-1),B1) feels like it ought to work to me. (Admittedly I had to null protect because FIND isn’t as good as INSTR, and I’ve written it in Excel, but the theme of the logic feels like it should work for the above.
~Will H (MYOB)
+15Probably not. If you're trying to group several numbers, it would be. Grouping by the string before the '.' should also work and would be much less complicated.
Daryl⚡Freelance Acumatica Developer (MVPx2🏆) for hire ⏩ linkedin.com/in/darylbowman
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.