Sage 100

We are are trying to create an SO invoice form in 2013 that will only print invoices that match a certain customer type. We did this in past versions by adding a Report Selection formula that essentials adds a SQL where clause for CustomerType = ""XX"". We created the standard inner join beween the SO_INvoiceWrk table and AR_Customer Crystal seems to ignore the report selection formula. Sage is telling us that this is by design as the worktable does the filtering and the Crystal form ignores the where clause. Has anyone else ran into this?

Try adding this into the Group Selection criteria instead. I've been lucky with this instead of Report Selection when dealing with work tables.

Thanks @AmberPrayfrock I think we tried this but I will try it again. I have never had trouble with this before, but it is the first time that I have done it on 2013.

@AmberPrayfrock We got a different result - so I think you may be on to something. I reset SQL. Thanks a bunch!

Instead of linking files, I create a UDF in Invoice Header and in the work table.

@MarkKotyla solution is very simple and straight forward. Very easy to do and will result in faster performance. Populate the UDF from the customer table.

Even with the UDF in the work file, you will still need to use Group Selection on FORMS instead of Record Selection. Record Selection only works on REPORTS.

Ok - we finally got this to work. We found that the link was not causing an issue as we tried to filter on an existing workfile. We used group selection with no success. We recreated the form from scratch and used the Group Selection and it appears to work. I think that Sage may have a bug. We placed a support case and they stated that we could not filter records in a form since they do this in a work table. This makes no sense. There is known programming issue in .NET where as the wizard that launches Crystal forms inserts a generic where clause. The .NET forums suggest that you REM this statement out as it will override the where clause withing the crystal report. This appears to be similar to what I am experiencing with Sage100. I wonder if they have a similar issue with their UI. In any case, Sage was no help and this group got me off high center. I think we needed to use the Group Selection as Amber and Beth suggested and we had a corrupted form. Thanks for everyone's help! I am going to look at speed. Mark's suggestion may also be beneficial to them as well.