[Jason Kaulback]

Hello,

I have been asked to create a report that shows the cash collected by team member. Each collections team member is assigned part of the alphabet to collect. An accounting team member posts the payments so I cannot generate the report based upon who enters the payment.

Does someone know how to create a report that would filter based upon the customer name (i.e. A-G for team member 1, H-N for tem member 2, etc.)?

Thanks,

Jason
This is a cached copy. Click here to see the original post.

[torin@aminian.com]

I would try to do this using a saved search. In the results tab try using the formula – Case WHEN {Field.Id} BETWEEN ‘A’ AND ‘G’ THEN {Field.Id} Else 0 END. On this result set the summary type to sum.

[Jason Kaulback]

Trying to add a formula – what field do I choose? None of the parameters I have chosen allow me to add a formula, they are all blocked out.

[torin@aminian.com]

The field you would use to add a formula to a saved search is in this case ‘formula (currency).’ The formula section will be available after choosing this field.

I would also create two more results to test the fields you are going to add in the formula. You are going to need to know which field ID to use for cash collected and customer name. Add two more formula fields and experiment with different field id’s until you have found the ones you are looking for. Then add those fields to the formula.

[Jason Kaulback]

This is what I tried, to no avail – coming back as ‘invalid expression’:

CASE {custcol_2663_companyname} WHEN between ‘A’ and ‘G’ THEN ‘Jorge Guzman’ ELSE 0 END

[torin@aminian.com]

It looks like the WHEN needs to come right after the CASE. Also, the ‘Jorge Guzman’ part is not quite right and I’ll explain below. Also, does each team member get a portion of the alphabet and customers are not shared? If so, you do not need to include the collections member name.

The formula would look like this- CASE WHEN {custcol_2663_companyname} between ‘A’ and ‘G’ THEN {fieldID2} ELSE 0 END.

{fieldID2} needs to be the field ID for cash collected.

If the same customers (A-G) are shared by two or more collections member then you will need to specify the collections member in the formula.

The formula would look like this – CASE WHEN {custcol_2663_companyname} between ‘A’ and ‘G’ AND {fieldID1} = ‘Jorge Guzman’ THEN {fieldID2} ELSE 0 END.

You will need to find the correct field ID’s for the two placeholders I included above.

For {fieldID1} you need to find the field ID that holds the collections member’s name.

{fieldID2} is cash collected.

Let me know if there is anything I need to clarify.

Torin Salerno

Aminian Business Services

NetSuite Support: support@aminian.com

(949) 407-8416 | (888) 800-5207 x 16

[Jason Kaulback]

Torin,

Thanks so much for your help – making progress on something I’ve never used before.

The first part worked, I can get cash collected for customers A-G. The next section is for customers H, M, O, S, T, U – here is what i wrote, is the EITHER statement the way to go?

CASE WHEN {custcol_2663_companyname} either ‘H’ or ‘M’ or ‘O’, or ‘S’ or ‘T’ or ‘U’ THEN {paymentevent.amount} ELSE 0 END

[torin@aminian.com]

You’re very welcome. In order to specify customers in this way I would use the LIKE function. The formula should look like this – CASE WHEN {custcol_2663_companyname} LIKE ‘H%’ or ‘M%’ or ‘O%’ or ‘S%’ or ‘T%’ or ‘U%’ THEN {paymentevent.amount} ELSE 0 END. Just make sure to use % behind the letter. Also, note that this function is case sensitive.

Suite answer ID 30998 provides other ways that the LIKE function can be used.

Let me know how the formula works.

[Jason Kaulback]

Torin,

I coped/pasted what you suggested, but with no luck.

[torin@aminian.com]

I’m guessing you got an invalid expression error. I tested this and got the error as well. The solution is going to be a little tedious but it works when I do this:

CASE WHEN {custcol_2663_companyname} LIKE ‘H%’ OR {custcol_2663_companyname} LIKE ‘M%’ OR {custcol_2663_companyname} LIKE ‘O%’ OR {custcol_2663_companyname} LIKE ‘S%’ OR {custcol_2663_companyname} LIKE ‘T%’ OR {custcol_2663_companyname} LIKE ‘U%’ THEN {paymentevent.amount} ELSE 0 END.

You would have to do this with each letter. It does require some copying and pasting but doesn’t take too long.

Try this, let me know if you have any luck.

[Jason Kaulback]

Torin,

Worked perfectly, thanks! I really appreciate the help.

Jason

[torin@aminian.com]

No problem at all. Before you finalize the report I want to let you know that I did notice a serious issue when I ran some testing using the BETWEEN condition. When I was looking for customers A-C using the formula I was only getting customers A-B. It seems like it is going up to and not including ‘C’ customers. Not sure if this is how it is supposed to function or if it is a defect. I would definitely look into this to see if it is functioning this way on your end. A simple workaround is to include one letter after what you are looking for. And when you get to ‘Z’, use the LIKE function to include Z.

[Author]

Posts