Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Eliminate specific records in transactions

Eliminate specific records in transactions

Eliminate specific records in transactions

I have a little problem - not sure if it is trivial however can't seem to make it work.
I have a simple formula:
if {table1.transaction} in
['tra1', 'tra2', 'tra3', 'tra4', 'tra5]
then {table2.amount}
else 0

Now, I have another transaction - call it 'sometra'
so what needs to happen is when {table1.transaction}='sometra'
then i want to make 'tra2' and 'tra3' from table 1 to show $0 instead of the amounts which are legit but actually doubling the end result.
Please help, thanks

RE: Eliminate specific records in transactions

Your request is not clear. It would be helpful if you provided some sample data and your expected results.


RE: Eliminate specific records in transactions

Hi Pete,
well, the data in table1.transaction is really a column showing transaction codes so they are in the format TR01....TR15 and some other codes, so basically when I have these codes I want to pull money amounts from table2.amount column. the formula the way I described it works however I need to show TR01 and TR03 as zero when I incur OCC1, otherwize in the grands scheme of things the final amounts will be doubled (SYS2=TR01+TR03) not sure if this helps for better understanding.
Thanks again.

RE: Eliminate specific records in transactions

No, still unclear.

According to your formula, an amount will only be other than 0 when the Transaction Code starts with TR, so any other code (ie, those starting with WA, SYS or OCC) will always return a 0.

The way you describe the problem suggests that OCC1 will be part of the same record (ie row), but your sample data suggests otherwise. Your explanation of the problem, doesn't fit with the sample data.

Please confirm the data structure is as your sample suggests (ie comprising just 2 columns of data), and provide a mock up showing what you expect the report to show, based on that sample.


RE: Eliminate specific records in transactions

Hi Pete,
hmmmm... it's a bit more complicated.
the formula I described is used in a subreport which passes a variable to the main report showing all transactions considered as adjustments or other fees (many codes). The SYS2 and WA are not included in the formula (or in the many codes in the formula) because they are called in other sub-reports that will calculate transactions current (i.e. WA), past due (SYS2). With OCC1, that is another standard transaction and in most cases will not affect the results as it is a transaction on it's own, and used too in a separate sub-report.
In general terms, I need the TR's to return their values, except unique situations when there is an OCC1 and the TR01/03 in the same account, in which case I would need the TR's to be artificially made zero otherwise they will add up with the SYS2 - therefore doubling the value. I cannot make the zero in the dataset unfortunately, otherwise all would be clear as crystal smile
eventually I'll try some mock-up hardly so as the whole report is quite complicated (yeah.. bills - everybody loves bills when they come in negative - hardly ever happens)
anyway, attaching a picture.

Balance - the legit amount
Payment - 0 (hmmm....)
Adjustments - that's where the problem lies because in this particular case the TR01 and 03 are read as they should except they should not be there in the data-set.
because they are there,
the past due amount, another simple formula adding up previous balance and payments and the adjustments get's doubled.
Hope this helps a bit more

RE: Eliminate specific records in transactions

This is very confusing, as you are showing the display, rather than the underlying design of the report. We also cannot tell whether these codes you mention are all instances of the same field or different fields. I think it would help if you showed a screenshot of the report in design mode and then added an explanation of the fields used for Occ1 and other codes.


RE: Eliminate specific records in transactions

Thank you all for your kind answers and sorry about confusions. Truly appreciate your time and patience.
Got this solved by another senior member in our team, it was quite a bit more that I thought would be - as it happens.
I can reproduce at a point in time what was done if may help others... at the end of the day the sub-report that was calling the adjustments was modified adding 2 more running totals, couple extra formulas and re-adjusting the variable formula for "adjustments".
Still learning.
Again, Thank you all.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close