Sage 100

I have been working on a Crystal Report (v10.0 for Sage) with Sage ERP 100 Standard v4.30.23 Inventory and Purchase Order module. I have a main report sorting by Inventory ItemNumber and a Sub Report sorting by matching (ItemNumber) PO detail line Required Date. I also have a Shared Date Variable between the two reports. My two problems are on the Sub Report. What is the syntax to cause the Sub Report to read the next record, because the shared variable for first Item on the main report is blank and the second item on the main report is getting the Sub Report's first record's date, etc? My second problem is the shared variable needs to be reset to bank after each record is printed. What is the syntax to do that in a formula field? Thanks in advance for any guidance. Here is my Shared Variable formula: WhilePrintingRecords; Shared DateVar POExpDate := {PO2_PurchaseOrderEntryLine.RequiredDate}

Michael...you've got a lot going on there. First of all, you know you can only used the shared varialble in the main report in a section after the subreport has been generated(printed) right? Second, the sub report can't get the next item # from the main report's dataset. You could (probably) get the next item # in the main report and send it to the subreport. Not sure that's what you need to do though.

Suggestion: add more detail sections, put the subreport in the first one, then your actual detail in the main report in the next, then another detail section with a formula that resets the shared variable but prints just """". Separate sections should get Crystal to process variables in the order you want. To reset the datevar use Shared DateVar POExpDate := date(0,0,0)

Phil, date(0,0,0) is a good tip. If you print that variable on the report via a formula what shows? Just a blank field? I assume Crystal would treat the formula as a date type formula, right?

@BrettLyder - just to be sure it doesn't print anything, I would do this: Shared DateVar POExpDate := date(0,0,0); """" That way you reset the variable and tell Crystal not to print anything, no matter how the field gets formatted..

@PhilMcIntosh - yeah that'd work. I guess i was really just curious what date(0,0,0) actually does to that variable. Is it null? It's not that big of a deal really, i'm having a hard time visualizing a scenario when I would need to check it.

As best as I can tell, datevar := date(0,0,0) is the date equivalent of numbervar := 0 and stringvar :=

What is the report trying to accomplish?

The report is trying to list the date into the future from the PO2_RequiredDate for a range of inventory items. If an item has an on-hand qty of zero then the report displays the PO2_RequiredDate. The subreport sorts the records with the furthest PO2_RequiredDate first and selects that date to pass to the main report. This way the ""ETA"" date is a date from the most recent PO issued for this item. The report is generated once a week and distributed to salespersons. If quantities are received from an ""older PO"" then they are displayed as ""on-hand"" on next week's report. Some Items will show a quantity on hand of zero and an ""ETA"" with a blank date. That means no POs in the system for that item.

How are your Access skills?

Null or := """" or := date(0,0,0) or :=0. Sorry, its on my ""bucket list"" ;)

I will work with @PhilMcIntosh suggestions this evening and let you know if I make progress. Thanks!

I also suck in Access but I have someone who knows much more than me. And when I really need the big GUNS, @DanBurleson is just phone call away. I would think that your project would be so much easier using a select query pulling data from PTQ's in Access. We did something similar only from a sales order standpoint (listing the on hand balance from IM and then listing all of the SO lines relating to the item directly below the item) for the salesman on a weekly basis. We did manage to make it work but it took 65 minutes every Monday. So we redirected it to the same tables via Access PTQ's and it ran in under five minutes. Just my two cents.

@PhilMcIntosh I'm real close to finishing this Crystal Report. I followed your instructions, but have one problem. The shared variable in the subreport contains the correct date from the PO2_PurchaseOrderEntryLine.RequiredDate. But the date that is carried to the main report via the variable is a different date. I placed the subreport in Details a (which will be suppressed) and the main report fields are in Details b. I thought the whole purpose of the shared variable, was to ""transfer"" data from the subreport to the main report. The shared variable in the subreport is forumla field defined like this: WhilePrintingRecords; Shared DateVar POExpDate := {PO2_PurchaseOrderEntryLine.RequiredDate} The shared variable in the main report is a formula field defined like this: WhilePrintingRecords; Shared DateVar POExpDate Is there another way to ""carry"" data from a subreport to the main report?

I don't think you can suppress Details A but you can overlay Details B in the section expert. Are you resetting the date variable in a section after Details B?

This is a similar thread: https://90minds-com.socialcast.com/messages/13974385

@MichaelMcDonald - @ThereseLogeais is correct - if you suppress the section the formula won't be processed.

@ThereseLogeais and @PhilMcIntosh and @JeffSchwenk Thank you for all your help. I now have discovered what I need to do next to get this Crystal Report to work. I looked in the help sections of CR v10.0 and v11.5 but could not find instructions - How to read only the first record in my subreport and then stop. - My subreport contains all Purchase Orders that are in PO module for each inventory item sorted in desending date order (the latest PO listed first). The latest PO Required Date is what I want to pass to the main report via a shared variable. Some of the inventory items will have up to 10 POs in the PO module and some may not have any POs in the PO module. In the case of no POs on file the date would be blank. Is there a special record counter field that I could query as the subreport reads/prints records?

In your subreport, add a group by itemcode, and use the Count summary on PO# and the Maximum summary on the Expected Date.

We've done something similar for inventory transactions, reading the most recent record. Unfortunately for you, it was in Access.

@PhilMcIntosh - I was able to finish my Crystal Report using the Maximum function. I would have never guessed that the Maximum function would work with a date field. I did not have to add a group by itemcode - I changed my Shared DateVar in the subreport to look like this: //SubReqDate WhilePrintingRecords; Shared DateVar POReqDate := Maximum ({PO2_PurchaseOrderEntryLine.RequiredDate}) I then used @JimWoodhead' instructions on how to suppress a subreport without losing the variables from the subreport following these instructions: https://90minds-com.socialcast.com/messages/13974385 Thanks everyone!

@MichaelMcDonald -I'm curious, how long does this report take to generate?

About 15 seconds on a stand alone Win XP Pentium 4 - 3GHz 1.5GB Ram machine searching only 21,050 Inventory Items and 383 Purchase Order records. I'm guessing it will be slower running on the client's network.

Wow, faster than I would imagine. LMK regarding the client's side of things.

@JeffSchwenk I used GoToAssist and uploaded the Crystal Report to the client's MAS 90 system today. Still connected to one of their ""older computers"" with GTA, I ran the Crystal Report and previewed. It took 5 minutes to generate a 25 page report using their live data.