Variables or Multiples IF then Else???

[computerlady143]

I not that good at variables and it possible I don't need one. I am attaching my report with my explanation. I have designed this Crystal report. I have to have every Item ID that is listed in this report. And only these Item ID. There are lots more but I can only have the one listed in the report. If I also tried using a Parameter field but that didn’t work. All the data fields I am using is pulling from that the list the data without using records. However, to get the Available date and that is my sole problem with the report.
The Formula field I created is : {JrnlHdr.JrnlKey_Journal} = 4 Then {JrnlRow.RowDate} Else {JrnlHdr.GoodThruDate}
{JrnlHdr.JrnlKey_Journal} = 4 (which means the item has been received on an A/P invoice
{JrnlRow.RowDate} is the date the item was received on that Invoice. {JrnlHdr.JrnlKey_Journal} = 10 (which means the Item is on a P.O. - {JrnlHdr.GoodThruDate} is the estimated date the item is to be received on the P.O. usually a month later. However, the item can be received longer that what on the P.O. This is not the date of the Purchase Order.
When I use the Select Expert this is my formula to select the records :

{JrnlHdr.TransactionDate} in {@Start Date} to {@EndDate} and
{LineItem.ItemID} in ["4651110", "4651210", "4651310", "4651410", "4651510", "4651610", "4651710", "4651810", "4653110", "4653210", "4653310", "4653410", "4653510", "4653610", "VM-0001", "VM-0080", "VM-0087GEA", "VM-0093", "VM-0094", "VM-0096", "VM-0097", "VM-0098", "VM-0194GEA", "VM-0227-NVST", "VM-02CA", "VM-0311GEA", "VM-03CA", "VM-0418-NVCT", "VM-0566GEA", "VM-0643GEA", "VM-137F-TC", "VM-137K-TC", "VM-137Q-TC", "VM-137TL-TC", "VM-140GEL", "VM-1412-NVCTEA", "VM-1482GEA", "VM-1510-WHCTEA", "VM-1512-WHCTEA", "VM-1517-COXEA", "VM-1575-WHCTEA", "VM-1619-NVSTEA", "VM-161GEL", "VM-1703G", "VM-1714GEA", "VM-1716GEA", "VM-1725GEA", "VM-1740-NVCTEA", "VM-1780GEA", "VM-1947", "VM-1oz MEDCUP-SLEEVE", "VM-200", "VM-2202-VB", "VM-2202-VW", "VM-2204-VB", "VM-2204-VW", "VM-2206-VW", "VM-2216-BARIEA", "VM-2302-05EA", "VM-2302-06EA", "VM-2302-07EA", "VM-2302-08EA", "VM-2306-05EA", "VM-2306-06EA", "VM-2309-05EA", "VM-2309-07EA", "VM-2311-00EA", "VM-2314-00EA", "VM-2314-04EA", "VM-2314-07EA", "VM-2315-05EA", "VM-2315-07EA", "VM-2315-09EA", "VM-2322-07EA", "VM-2322-08EA", "VM-2327-08EA", "VM-2332-05EA", "VM-2332-06EA", "VM-2332-08EA", "VM-2335-05EA", "VM-2335-06EA", "VM-2335-07EA", "VM-2335-08EA", "VM-2339-07EA", "VM-2339-08EA", "VM-2341-07EA", "VM-3001", "VM-3003", "VM-3004", "VM-3008KIT", "VM-3009", "VM-3010-NVST", "VM-3015-NVST", "VM-3035-NVST", "VM-3570-NVST", "VM-3580-NVST", "VM-60-01EA", "VM-6006", "VM-6354", "VM-6472", "VM-6550", "VM-6560", "VM-6565", "VM-6566", "VM-6570", "VM-6575", "VM-6580", "VM-6585", "VM-6610", "VM-6620", "VM-6625", "VM-6630", "VM-6645", "VM-6654", "VM-6660", "VM-6672", "VM-6824", "VM-7080-NVST", "VM-7202GEA", "VM-7221GEA", "VM-7243GEA", "VM-7420GEA", "VM-7440GEA", "VM-7840GEA", "VM-8010", "VM-8020", "VM-8120", "VM-8197S", "VM-9001EA", "VM-9003EA", "VM-9004EA", "VM-9005EA", "VM-9006EA", "VM-9007EA", "VM-9008EA", "VM-9009EA", "VM-9010EA", "VM-9011EA", "VM-9012EA", "VM-9060", "VM-9172", "VM-9174", "VM-9180", "VM-9866GEA", "VM-9867GEA", "VM-9868GEA"] and
{JrnlHdr.JrnlKey_Journal} in [10, 4].

However, by using this it only selects the items but only if they are {JrnlHdr.JrnlKey_Journal} in [10, 4]. But then the problem are:
1. It doesn’t select the Items listed in the formula if they are not {JrnlHdr.JrnlKey_Journal} in [10, 4], which I need because I could have Items that are not listed on a P.O. or A/P invoice.
2. Also, if there is a P.O. that has been received on an A/P invoice and paid it will list that {JrnlRow.RowDate}. But there can be another A/P invoice that has received that Item and {JrnlRow.RowDate} should be listed because it is the latest time the Item was received. The invoice that has been paid 99% of the time is listed on a closed invoice and closed P.O.
3. Also, I need it to pick up the latest open P.O. {JrnlHdr.GoodThruDate} if it is later than than any A/P invoice that might be paid or unpaid.
4. If the item is listed and not on any P.O. or received on any A/P invoice then I need that item listed also.
If I use this formula:

{JrnlHdr.TransactionDate} in {@Start Date} to {@EndDate} and
{LineItem.ItemID} in ["4651110", "4651210", "4651310", "4651410", "4651510", "4651610", "4651710", "4651810", "4653110", "4653210", "4653310", "4653410", "4653510", "4653610", "VM-0001", "VM-0080", "VM-0087GEA", "VM-0093", "VM-0094", "VM-0096", "VM-0097", "VM-0098", "VM-0194GEA", "VM-0227-NVST", "VM-02CA", "VM-0311GEA", "VM-03CA", "VM-0418-NVCT", "VM-0566GEA", "VM-0643GEA", "VM-137F-TC", "VM-137K-TC", "VM-137Q-TC", "VM-137TL-TC", "VM-140GEL", "VM-1412-NVCTEA", "VM-1482GEA", "VM-1510-WHCTEA", "VM-1512-WHCTEA", "VM-1517-COXEA", "VM-1575-WHCTEA", "VM-1619-NVSTEA", "VM-161GEL", "VM-1703G", "VM-1714GEA", "VM-1716GEA", "VM-1725GEA", "VM-1740-NVCTEA", "VM-1780GEA", "VM-1947", "VM-1oz MEDCUP-SLEEVE", "VM-200", "VM-2202-VB", "VM-2202-VW", "VM-2204-VB", "VM-2204-VW", "VM-2206-VW", "VM-2216-BARIEA", "VM-2302-05EA", "VM-2302-06EA", "VM-2302-07EA", "VM-2302-08EA", "VM-2306-05EA", "VM-2306-06EA", "VM-2309-05EA", "VM-2309-07EA", "VM-2311-00EA", "VM-2314-00EA", "VM-2314-04EA", "VM-2314-07EA", "VM-2315-05EA", "VM-2315-07EA", "VM-2315-09EA", "VM-2322-07EA", "VM-2322-08EA", "VM-2327-08EA", "VM-2332-05EA", "VM-2332-06EA", "VM-2332-08EA", "VM-2335-05EA", "VM-2335-06EA", "VM-2335-07EA", "VM-2335-08EA", "VM-2339-07EA", "VM-2339-08EA", "VM-2341-07EA", "VM-3001", "VM-3003", "VM-3004", "VM-3008KIT", "VM-3009", "VM-3010-NVST", "VM-3015-NVST", "VM-3035-NVST", "VM-3570-NVST", "VM-3580-NVST", "VM-60-01EA", "VM-6006", "VM-6354", "VM-6472", "VM-6550", "VM-6560", "VM-6565", "VM-6566", "VM-6570", "VM-6575", "VM-6580", "VM-6585", "VM-6610", "VM-6620", "VM-6625", "VM-6630", "VM-6645", "VM-6654", "VM-6660", "VM-6672", "VM-6824", "VM-7080-NVST", "VM-7202GEA", "VM-7221GEA", "VM-7243GEA", "VM-7420GEA", "VM-7440GEA", "VM-7840GEA", "VM-8010", "VM-8020", "VM-8120", "VM-8197S", "VM-9001EA", "VM-9003EA", "VM-9004EA", "VM-9005EA", "VM-9006EA", "VM-9007EA", "VM-9008EA", "VM-9009EA", "VM-9010EA", "VM-9011EA", "VM-9012EA", "VM-9060", "VM-9172", "VM-9174", "VM-9180", "VM-9866GEA", "VM-9867GEA", "VM-9868GEA"]

It will list all the Items but then it won’t give me an available date of any kind.

This is the formula field for my Available Date:

IF {JrnlHdr.JrnlKey_Journal} = 4 Then {JrnlRow.RowDate} Else {JrnlHdr.GoodThruDate}

But I know this is not correct because I need it not to list a date but the Item if it is not on a P.O. or been received or listed on an A/P invoice.

 

[Madawc]

90% of what you posted is irrelevant to what you are asking - I think. It seems your selection works as it should, but you want something related to the Available Date. It's not clear what.

If you're wanting to list items lacking particular data, you need to select all relevant records, group by item, suppress item detail lines and show or suppress the details in the group heaader. Does that help?

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[computerlady143]

But the problems is the items that aren't listed on a P.O. or A/P invoice will have no records. The information is drawn that is pulled from using a function called additional function will it just gets the data not using records. So then that data doesnt get listed. If I don't select records I get all data for all Items but the Avaialbe date. If I used record selections then I get the data for those Item ID, which is not all Item ID.But I get the date.

 

[Madawc]

Maybe you need to select more, then suppress the items that aren't listed on a P.O. or A/P invoice.

Or use a Left Outer: look it up using SEARCH if this is new to you.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP