Method Community

I am trying to create customer transaction reports for sales orders and invoices.  For sales, I would like to have a sales order report to deliver transactions within a date filter that can be entered on demand.  Same for invoices.

I have created the report in the report designer with the fields needed but cannot use the report links to find them.  I assume this is because they are not based on the customer table but, rather, their respective transaction table.  I have reviewed the videos that instruct on customizing a screen but that give a static filter and I need the filter to be altered at the time of report generation depending on need.

Hi Retrotel,

Can you clarify what you mean by "cannot use the report links to find them"? It seems you have created the report in the Report Designer, but how are you trying to access the report in Method?

Please let me know.

-Russell

For instance, under the customer tab, there is a link for customer reports.  I have created an invoice report using the invoice table, but there is no link to open and add filters to that report like there is for reports using the customer table.  When I create a button or link on a screen to open the invoice report, I have to apply the filter at that point which applies the same filter every time.  I would like to be able to open a report that will show me customers and invoices for, let's say, last week.  Then, maybe apply a filter by rep or use a different date range.

Hi Retrotel,

Thanks for the clarification. You are correct, on the CRM_CustomerReports screen, the reports in the dropdown are filtered to show those with Contacts as the MasterTable. You could copy that screen and remove the filter from the dropdown, or you could generate the report in another way, such as a button which creates the report without the user needing to go through the wizard. 

Hope this helps, if you have any further questions just let me know.

-Russell

That is a big help.  One step closer.  Now the question is how to filter by invoice.txndate field.  I had assumed that the filters were determined by the fields on the reports but it looks like they are set up on the screen.  Is there a simple way to convert the next action filter to transaction date?  Or do I have to create the action line by line?

Brian

Hi Retrotel,

Sorry for the delay in my reply. Filters can be applied by the screen or the Report Designer, it depends on how you want to filter the report. One limitation with the Report Desinger is that it cannot filter based off of values on the screen that is generating it, only by values in the database. So for example if you wanted to have the user enter a date each time the report was generated, you would have to setup the filter using actions on the screen. 

Can you explain what you mean by the next action filter? I'm assuming you want to give your users the option to select what type of filter to be applied to the report each time? If so, this would have to be setup on the screen due to the reasons above? Can you also explain what filter options you want to give your users to filter on the date field?

-Russell

Sure - basically, salespeople need to assemble a list of records to call.  On any given day they will want to call on records that had invoiced two weeks ago.  Another day they may want to look at invoices from last week.  I have gone so far as to copy the report screen (new name  RT_CustomerReports).  I removed the main filter.  The report runs in report designer but not from the new report screen.  It gives me an error telling me to make sure the report will run in designer, which it does.

I would like to be able to filter based on the invoice date field but that option does not show up on the report screen.

Hi Retrotel,

By removing the main filter, do you mean you removed the resultReportFilter action result? Did you also remove it from the Where Script in the Generate Report action settings? If you post the full error message here I might be able to give some insight.

However if the only filter you need is on the date field, you should be able to make this much simpler. Your filter will need start and end dates, and you could generate these dates automatically in some cases, like for "two weeks ago" using Date Add actions with negative values (http://www.methodintegration.com/method/kb.aspx?folder=methodcrm&article=Date-add&id=137). For example start date = current date-14 days, end date = current date-13 days.

The default Genrate Report action on the screen uses an advanced script to filter, which is much more powerful but more prone to errors if the where script isn't formatted properly. This may be the issue you are facing. Instead, you can use a Regular Script with 2 Where conditions, one where date > start date, the other where date < end date. If you wanted you filter by sales rep as well, you could add a 3rd where condition. For more information please see http://www.methodintegration.com/method/kb.aspx?folder=methodcrm&article=Generate-Report&id=151.

If you have any questions about this just let me know.

-Russell

Here is the exact error:

Error^Action Sequence Order:138^ERROR: There was an unspecified error loading the report. Please check your Generate Report action to ensure all filters are setup properly, and that the report loads correctly in the report designer.

Hi Retrotel,

This is most likely caused by the advanced script filter in the Generate Report action like I suspected. If you changed the actions that build resultReportFilter, the advanced where script may not be in SQL format and if so would prevent the report form generating.

-Russell

I didn't change any actions.  Are all of the reports going to be this difficult?  We are struggling to povide a simple filtered list of records for our salespeople to call.  I am struggling to justify this product as a sales tool.  So far my people are screaming to go back to the lousy Act! product.

Please help.

Hi Retrotel,

If the actions are the same, then the advanced script filter is probably trying to filter on fields that exist in the customers table, but not the invoices table (or whatever table you are using).

I think you would find it much easier to make a new screen to generate the report from, rather than copying and modifying the existing one. The existing one was designed with the customers table in mind, and all of the actions build a filter specifically for the customers table. Does my alternate suggestion from this post make sense: http://method.me/cs/forums/p/5802/22078.aspx#22078? 

-Russell