Howdy Howdy,
I am experiencing a very perplexing issue and I wanted to throw it to the community to see if there was any insight into the problem.
ABS-VENDSUM is a clone of the Bills and Adjustments Generic Inquiry with less results grid lines and a few more conditions. Essentially it pulls all closed bills and adjustments, properly converts debit adj into a negative value, groups them and while grouping it sums the total values. Simple.
ABS-CHECKSPAY is a clone of the AP - Checks and Payments Generic Inquiry with less results grid lines and a few more conditions. It was determined that measuring cashflow via checks instead of bills was more accurate which I cannot argue. However when applying the same logic to the APPayments table everything works identical until we attempt GROUPING the output. At this time it selects a single transaction and presents that as the value. You can see that the logic is working because ungrouped it shows all line items it should show and it correctly turns them negative and it totals out to our test data.
I believe the issue lies with =IIf( [APPayment.DocType]='ADR', (-1 * [APPayment.OrigDocAmt]), [APPayment.OrigDocAmt] ) which is a formula to convert debit adj to a negative value because it is positive in the line details. If this is removed APPayments will successfully group all the lines but the sum is incorrect because values which should be negative are now positive.
Is there any insight into this strange behaviour between two very simple tables?