AZdesertdog
IS-IT--Management
Using CR10/CE10 against MSSQL2k dbase.
I have two tables, one is a HEADER that contains a balance field for each invoice and a second table is the DETAIL table which can have multiple rows. Invoice is the join field. I want to be able to use HEADER fields in conjunction with DETAIL fields but don't want the redundancy that comes with doing that. In my current layout you see the 4 DETAIL rows that comprise invoice 12345 and if I bring in the Balance (from HEADER), it shows the 590.00 for each line. I'm trying to create a formula that will, for each Invoice number, only display the Balance (or any other HEADER field I choose) from the lowest line# and for all other rows be zero.
I know this would be easy with running totals or a three formula setup in the report but I need to do it in one formula so that I can use it in an Element of my Business Views. The goal is to allow users to have fields from both the HEADER and DETAIL table but not change their level of granularity so they can use sums for all fields.
Any help is appreciated. Thx.
Current data layout
Line# Invoice Amount Balance
1 12345 475.00 590.00
2 12345 75.00 590.00
3 12345 20.00 590.00
4 12345 20.00 590.00
Desired layout
Line# Invoice Amount @NewFormula
1 12345 475.00 590.00
2 12345 75.00 0
3 12345 20.00 0
4 12345 20.00 0
I have two tables, one is a HEADER that contains a balance field for each invoice and a second table is the DETAIL table which can have multiple rows. Invoice is the join field. I want to be able to use HEADER fields in conjunction with DETAIL fields but don't want the redundancy that comes with doing that. In my current layout you see the 4 DETAIL rows that comprise invoice 12345 and if I bring in the Balance (from HEADER), it shows the 590.00 for each line. I'm trying to create a formula that will, for each Invoice number, only display the Balance (or any other HEADER field I choose) from the lowest line# and for all other rows be zero.
I know this would be easy with running totals or a three formula setup in the report but I need to do it in one formula so that I can use it in an Element of my Business Views. The goal is to allow users to have fields from both the HEADER and DETAIL table but not change their level of granularity so they can use sums for all fields.
Any help is appreciated. Thx.
Current data layout
Line# Invoice Amount Balance
1 12345 475.00 590.00
2 12345 75.00 590.00
3 12345 20.00 590.00
4 12345 20.00 590.00
Desired layout
Line# Invoice Amount @NewFormula
1 12345 475.00 590.00
2 12345 75.00 0
3 12345 20.00 0
4 12345 20.00 0