Sage 100

I need some help please: version 4.45 SQL Customer wants to print AR Statements for the first time ever, starting tomorrow. They want a ""quick change"" to the statement format. They would like to exclude from the statement, all credit memos that are older than 180 days. I tried using a formula in select expert, and also tried suppressing the detail lines with the same formula. Neither approach is working. I need another set of eyes. here's the formula I'm using: ({AR_StatementWrk.InvoiceType}=""CM"") AND ((CurrentDate-{@InvoiceOrTransactionDate}) < 180)

I would use a datediff function to get the number of days between the two dates. BUT, I think you are opening a can of worms. The statement balance and the aging buckets are going to include everything. Getting everything to add up is going to be the hard part.

Do they also want it left out of the totals at the bottom? Those totals aren't summaries, they are fields in the file. So if they need the totals to be correct, you won't be able to use the select, you'll HAVE to use the suppress.

Great minds, @BrettLyder !

Yep, I was just reading your post! I've had a few of these requests over the years and it seems like every time you hide something ""here"" it screws up something ""over there""!

Suppress isn't working. I will try the datediff function. I created a formula to display the results of my formula and they seem to calculate right, that's why I don't understand why the section expert suppress isn't working.

I got this to work in suppress: (({AR_StatementWrk.InvoiceType} = ""CM"") AND (DateAdd (""d"",180,{@InvoiceOrTransactionDate}) < CurrentDate)) but it did not affect the totals

And nothing seems to work in the selection formulas...

For a FORM, you have to use the Select GROUP instead of Select RECORD. Sage ignores the Select RECORD. But Select Record does work on standard REPORTS.

Nice tip Beth! I didn't know that group selection formulas worked on forms that's cool!

OK. Thanks for the help. Got the detail lines to suppress using: ({AR_StatementWrk.InvoiceType}=""CM"") AND datediff(""d"",CurrentDate,{@InvoiceOrTransactionDate}) < 180= true

Thanks Beth. I'm not using the select record for the reasons mentioned above, but that is great info. I didn't know that the forms ignored record select statements.

A client taught me that, @DougHiggs . I learn a lot from them - like if you export a Financial report to a PDF, you can figure out which Crystal report is being used because PDF defaults to the Crystal report name.