GROUPING OF SINGLE RECORD AT TWO PLACES

[BHAT100]

I am making inventory report which have inter -site transfers in single table with single record with diffrent two fields transfer from and transfer to fields. This table also have transactions from sales, purchase and BOM.
I want to show this single record in transfer from and transfer to location group.

would previous field formula would work in situation

Any idea would be great help to me


-Bhat

 

[rosemaryl]

Dear Bhat,

Please give example data in record and example of what you want the report to look like.

In addition it is always helpful for the persons answering your question to know the version of Crystal and the database.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[BHAT100]

Thank you for your reply,

Crystal Report:9.0
Data base : Great Plains
Server: SQL SERVER

I am making Historical Inventory Report based on GL Posting date?

TABLES

-SOP30200 FOR GL POSTING DATE - SOP
-PM30200 FOR GL POSTING DATE - POP
-BM30200 FOR GL POSTING DATE - BOM
-IV30200 FOR GL POSTING DATE - INV
-IV00101 ITEM MASTER
-IV30300 ITEM HISTORY TABLE HAS QTY AND VALUE
-IV40400
-IV40700 LOCATION TABLE

I have following group

GROUP1-IV40700.LOCNCODE
GROUP2-IV00101.ITMCLSD
GROUP3-IV30300.ITEMNMBR
DETAILS: pulling qty and amount by formula's for each month
Footer 3
footer 2
footer 1

I am using follwing formula to qty for june:

if not isnull ({IV30200.TRXSORCE})and {IV30200.GLPOSTDT} <= cDateTime(2005,06,30,00,00,00)
then {IV30300.TRXQTY}*{IV30300.QTYBSUOM}
else 0

and same way for amount

formula in record selection:

{IV40700.LOCNCODE} = {?Location}

Now my problem:

Inventory transfer: transfer from and transfer to is in single record. it is just pick one side of transaction i.e for transfering from site, not to transfer to?

somebody guides me if I use following it should work
that I should create following parameter for location:

{IV40700.LOCNCODE} = {?Location}

and create another formula:

(?location) in [{iv30300.trxloctn},{iv30300.trnstloc}]

note:
{iv30300.trxloctn} = transfer from location
{iv30300.trnstloc} = transfer to location

but I am not able figure out where to place this formula and
Pull transactions for transfer To location

Any idea would great help to me

Thanks

-bhat

 

[rosemaryl]

Dear Bhat,

I am not sure what the issue is here. Are you not pulling correct records? How did you link that location table to your iv30300 Item History Table?

Are you trying to group once for Transfers from and transfers to? If so do you need to repeat the entire record or are you showing it on one line?

Sorry if I am confused but, maybe an example of what you want to see.

However, regarding the formula you were given, that makes sense to me and that would **Replace** your existing selection criteria.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[BHAT100]

Thanks for reply.
iv30300 has following record. this is main table of report.
DOC NUM TRANSOURCE QTY AMOUNT TRANFROM TRANTO
IVTFR00000001 TRF000000006 2880 353.37 06-WELNGTN 06-MAIN
IVTFR00000001 TRF000000007 3040 219.58 06-WELNGTN 06-MAIN

when I am using following formula to pull record. Being a single record it just pull once. there is no separate record for transfer to. it is same and single record which shows qty, qty and transfer from and transfer to site.
what I need to show the transaction once in transfer from site "nagative" and another in transfer to with "positive value"

if not isnull ({IV30200.TRXSORCE})and {IV30200.GLPOSTDT} <= cDateTime(2005,06,30,00,00,00)
then {IV30300.TRXQTY}*{IV30300.QTYBSUOM}
else 0

I hope I am able to explain it properly.

Thanks for your reply.

-bhat

 

[rosemaryl]

Dear Bhat,

Was is the function of the Transource field? To me this looks like two records? Is this not so?

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[BHAT100]

There is only one record for one transaction. the above are two transactions i.e
-trf000000006
-trf000000007

both transfer are in above example from 06-welington to 06-main

Thanks

-bhat

 

[BHAT100]

is there a value for parameter value.

parmeter group 1 - location
{IV40700.LOCNCODE} = {?Location}

where can I place following formula to value for
{?location}

(?location) in [{iv30300.trxloctn},{iv30300.trnstloc}]

Thanks

-bhat