Method Community

I want to do a simple report or grid with the following columns:

• Customer name
• YTD sales
• Marketing expenses
• Marketing expenses as a % of YTD sales (a calculated field)

Note that marketing expenses are a single item in the Items table (in the "Discounts" category).

Anyway, we have some old Method report screens with intricate calculations that I can use as a guide, but they seem overly complex. More specifically, columns were created in the Customers table like "YTDSales". I'd rather do a new screen.

My questions are as follows:

• Do I base the new screen on the Customers table?
• Do I need to create columns in the Customers table for YTD sales (in that case, I'll just use the existing one) and Marketing expenses. These would be places to deposit the calculated values as I loop through the Invoice table compiling sales and marketing expenses by customer. Is there a better way?
• Is it best to use a pivot report, or can this be accomplished with a simple grid? I would like to be able to filter the report by sales rep (a drop-down box above the grid or pivot grid).

Hi There Mike,

My apologies for the delayed response here, I must have missed your post.

To answer your questions

• Do I base the new screen on the Customers table?

Yes, For what you are describing. The customer Table is the common element in all the things you are describing here. Im assuming that the YTDSales field would be a field calculated based on Invoices/Sales Reciepts or perhaps Recieved Payments for that customer, and your Marketing expenses are likely also contained as line items for transactions made by this customer, correct?

• Do I need to create columns in the Customers table for YTD sales (in that case, I'll just use the existing one) and Marketing expenses. These would be places to deposit the calculated values as I loop through the Invoice table compiling sales and marketing expenses by customer. Is there a better way?

The way you are describing would be the best way. Having information stored on the Customer level would mean you could access it again from any screen, only having to do the calculation in one place. 

• Is it best to use a pivot report, or can this be accomplished with a simple grid? I would like to be able to filter the report by sales rep (a drop-down box above the grid or pivot grid).

I'm having issues pulling sales results. Here's what my code looks like.

I'm having an issue, here's what my actions  looks like for a button I’ve created:

1 Assign Value to Action Result - create resultWhereSales for use with date calculations

2 Assign Value to Action Result - Assign Start Date to resultEndDateTest

3 Assign Value to Action Result - Assign Start Date to resultStartDateTest

4 Date Function - Format resultEndDateTest as MM/dd/yy

5 Date Function - Format resultStartDateTest as MM/dd/yy

6 Start Loop Through Table - Loop through customer table

7 Start Conditional Statement - If no parent

8 Assign Value to Action Result - Assign FullName to resultCustomerName

9 Character Function - resultWhereSales = resultWhereSales + Merge resultEndDateTest

10 Character Function - resultWhereSales = resultWhereSales + Merge resultStartDateTest

11 Character Function - resultWhereSales= resultWhereSales + Merge Customer

12 Retrieve Value From Table - resultSalesAmount = sum of where resultWhereSales

13 Insert Records Into Table       

14 End Conditional Statement

15 End Loop Through Table

 Notes:

• The resultWhereSales for use when looping through the Invoice table looks like this:

(Customer = 'CustomerName:Main account') AND (TXNDATE >= 'StartDate' AND TXNDATE <= 'EndDate')

In the looping action, I replace “CustomerName” with the parent name for each customer. I also replace the “StartDate” and “EndDate” text with action results based off date-picker drop-downs on the screen.

• Regarding tables, I am looping through the Customer table (Action 6), pulling sales data from the Invoice table (Action 12), and inserting records into a custom “SalesandMarketing” table (Action 13)

Anyway, if, as a test I hard-code an actual customer name into the “CustomerName” part of the resultWhereSales script and then enter the value pulled from the Invoice table onto a field on-screen, I get a correct number. Keep in mind for this test I'm not creating records in the "SalesandMarketing" table (I removed the Action 13), I'm just entering the sales for a single customer into a field on the screen instead.

However, if I leave the script as-is, loop through the Customer table and try to pull sales results from the Invoice table for all applicable customers, then the SalesandMarketing table shows rows with all customers, but the sales are all $0.

Please advise.

OK, on a base level I got it to work.

After looping through the cCustomer table (to pull a fullname for "parents" and then using a "resultwhere"(and an additional "resultwhere2") script to retrieve values from the Invoice table, I then made all the action results blank. Clearing the action result vlaues during looping worked.

BUT...

Single quotes in the fullname are killing me. If I just pull the fullname for each customer parent, and merge it into my "resultwhere", the processing stops and Method tells me to replace single quotes in the fullname with two single quotes (not a double-quite character, but two single-quote characters). 

So I do that, but processing stops again because Method tells me that the fullname (eg. ABC''s Aesthetics) is not a valid dropdown listvalue for 'CustomerRef'.

First of all, I don't know what 'CustomerRef' is. Second, whether I replace single quotes with double quotes or not - either way the processing breaks down.

Please help!

Hey MikeB,

I've tried to duplicate what you were trying to create but I'm running into issues as well. For this report, I would the say the scope of what you are trying to achieve is beyond what we can cover over the forums. I would recommend you get in touch with our consulting team to complete the report.

-- Loi