Sage 100

Can anyone help me write some crystal code to translate SO_OrderDetail.PromiseDate into a fiscal period according to GL_FiscalYear?

If fiscal is based on calendar months, you can use this: month({SO_SalesOrderDetail.PromiseDate})

Unfortunately it's not and it changes every year.

Create a subreport with GL_FiscalYearDetail and pass the Promise Date into it. In the selection formula in the subreport enter something like {?Pm-SO_SalesOrderDetail.PromiseDate}<={GL_FiscalYearDetail.PeriodEndingDate}. This will give you all of the Fiscal Year Detail records after (or equal to) the promise date. Summarize the minimum fiscal period on the report footer, and hide everything else.

As I think about this more I am guessing it will not be possible - because it is an export. Any subreports will mess up the format of the excel file and I don't think it will be possible to derive the fiscal period in a single formula field.

You could create a script to populate a UDF with this value.

@MichaelNottoli that's a thought.

You can get it with a subreport. I can't say it's elegant.

@RobertWood but can I export it to Excel and keep the data in neat rows and columns?

Can you use 2016 Excel Power Query instead of Crystal?

@DanBurleson I don't know. The query is actually just a dump of SO_SalesOrderDetail & Header except for the fiscal period and some simple filters, so maybe - talk to me!

@GregStiles if you use this version and export it to Excel (Not the Data Only version), it seems to work.

Awesome @RobertWood but why do you think these particular dates are causing the year and period to be blank?

@GregStiles mostly because I cobbled it together in about 15 minutes and didn't double check my logic. I left a = sign out so if the date was a period end date, it returned a blank. In the subreport, you'd need to change the suppress logic on the details section. Here's an updated report.

@RobertWood fantastic! I'll insert this into my report and see how it goes - thanks!

If it doesn't export to Excel well, I know a technique which might help.

@KevinMoyes it does, but I would still like to know your technique?

I've had success with exports of crystal with subreports for use in excel by exporting as tab delimited file.

Pass the value to the main report using a shared / global variable. Put the sub-report in the section above, but don't fully suppress it. Have the value calculated in the sub-report within a ""hidden"" section (with everything else suppressed in the sub report). Sub-report formatting: suppress blank sub report. Main report section formatting: suppress blank section. This way the sub-report is executed (so the shared variable is calculated properly), but produces no output. Because you are bringing out the shared value in a normal main report formula field, it should export to Excel nicely.

Here's a quick 5 minute example, using Robert's report as a starting point.

That's cool, @KevinMoyes!

@KevinMoyes that is cool!

Linking solution: No code, no sub reports, no variables needed

Sweet. That is why this group is so helpful!

@DanBurleson WOW, wow, excellent!

Yes, a very nice technique @DanBurleson . For a report with simple grouping I think I'll start using that! It never occurred to me to use the <= type join for this! I had to come up with something for complex reports, needing sub-report data in the main report for math... and after I came up with the method above I tend to use it whenever I need results with odd linking, but sub-reports do add processing time. Adding another tool to the tool kit is always welcome!

Sure if you want to do it the smart way, @DanBurleson. :laughing:

A ""simple way"" is a beautiful thing!