pulling transactions having gl posting date in four tables

[BHAT100]

I am trying to pull inventory records by GL Posting Date.

Transaction tables is iv30300 ( inventory table having all records relating inventory which orignates in inventory and sales and purchase transactions orignates from sales and purchase orders modules.

I am using following formula to transaction from iv30300 table. but it does not return any value/transactions

if {BM30200.PSTGDATE} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}
else if {IV30200.GLPOSTDT} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}
else if {sop30200.GLPSTGDATE} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}
else if {PM30200.GLPSTGDATE} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}

Any idea would be great help.

Thanks

-bhat

 

[lbass]

How do you have your tables linked? What is your record selection formula?

-LB

 

[Madawc]

As lbass says, it depends on how the tables are linked - equals or left-outer.

I also suspect nulls are a cause, fields without a value, which stop Crystal if they are encountered without an isnull.

I'd write a set of boolian formulas: logicwhich will return 'true' or 'false'. Say @DateBM as:

not isnull ({BM30200.PSTGDATE}) and
{BM30200.PSTGDATE} <= cDateTime(2005,05,31,00,00,00)

Or it could be <= Date(2005, 05, 31) if your database does not use time. Do four of those, and then in the selection try

@DateBM or @DateIV or @Datesop or @DatePM

Note that a boolian can be invoked by name, without the need to test for 'true'. You can also comment out the selection and display the raw data with the boolians, if the test is still not working.

Madawc Williams (East Anglia, UK)

 

[BHAT100]

Thanks for your reply.

my tables fields are linked equal - inner joins.

I am mentioning the name of table first which is placed on left then right.


itemnmbr docnumbr docnumbr/sopnumbe
IV00101------iv30200 -------> iv30300 ---------->SOP30200
trxsource
IV30300------------->BM30200
docnmbr
IV30300------------>PM30200
trxid
BM30200-----------BM30300


Thanks again,

-bhat

 

[BHAT100]

Hi again,

My record selction formula is as follow.

{IV30300.TRXLOCTN} = {?Location}

and group are as below:

GRP1: IV30300.TRXLOCTN
GRP2: IV30300.ITEMNMBR

Madawac: database use date/time.

I am using formula in a coulmn because I want pull record by month say Jan , feb and so on..

Thanks

bhat

 

[Madawc]

If you use 'equal' joins, you'll only get a 'row' when all four types exist. The 'left outer' covers cases where you want everything in Table A, along with data from Table B if it exists. Where there is no Table B entry, that part of the 'row' will be nulls.

Madawc Williams (East Anglia, UK)

 

[BHAT100]

Thanks for your reply

individually
@DateBM, @DateIV, @Datesop, @DatePM formula's are picking up data for sales, purchase, inventory and BOM transactions.

but when I am using @DateBM or @DateIV or @Datesop or @DatePM together in single formula as mention above.it gives error " boolean is required" though boolean is already there in each formula.

Thanks

-bhat

 

[Madawc]

Double-check, maybe one of them isn't a boolian. Put on different lines and comment out one at a time, to see where the error occurs

Comment out the selection and display the fields along with the data.

Madawc Williams (East Anglia, UK)

 

[BHAT100]

Sorry Madawac for bothering, but I am stuck.

I am using following formula, if I am puting these formual individually in @qty_may formulla then it works but when I put then together in @qty_may then it gives error saying boolean is required.

@qty_may:=

@datebm or @datepm or @dateinv or @datesop


@datebm ( bill of material)
if NOT ISNULL ({BM30200.PSTGDATE}) and {BM30200.PSTGDATE} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}

@datepm(Purchasing)
if NOT ISNULL ({POP30300.GLPOSTDT}) AND {POP30300.GLPOSTDT} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}

@dateinv(Inventory)
if NOT ISNULL ({IV30200.GLPOSTDT}) and {IV30200.GLPOSTDT} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}

@datesop(Salesorder)
if not isnull ({SOP30200.GLPOSTDT}) and {SOP30200.GLPOSTDT} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}

Thanks again for your help

-bhat

 

[Madawc]

Boolians don't start witt an 'if'. You'd need NOT ISNULL ({BM30200.PSTGDATE}) and {BM30200.PSTGDATE} <= cDateTime(2005,05,31,00,00,00), which will return 'true' or 'false'.

Madawc Williams (East Anglia, UK)

 

[BHAT100]

Dear Madawc,

I have tried above options but whenever I am trying pull records of all tables it does not work. As I said before, IV30300 have all transactions relating to inventory from sales, purchase ,BOM and inventory itself.but it does not have GL Posting date that I am pulling from SALES (sop30200),BOM bom30200),PURCHASE (POP30300) and Inventory (iv30200). These tables are left out linked iv30300. if I use formula separately for each tables then it pulls records from iv30300 tables

if {POP30300.GLPOSTDT} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}
else 0
and so on from other tables i.e. SOP30200, iv30200, BOM30200

This way if I am putting above formula in four coulmns SAPERATELY it shows records relating to respective table.
(GL POSTING DATE)

but I need records from all the table in one column and if I use combined formula for above together then it does not retrun any data

I have declared NULL in record selection.

Any idea to solve above would be great help to me

Thanks

-Bhat