Adding monthly sales for reports in Advanced Pack

[northstrider]

Hi there support team!

I'd like to ask, how can I add/import monthly sales for reports in Advanced Pack?

Thanks in advance!

[tanya]

Hello
Do you mean, how to build the report?

if yes, you need to create grid report for Opportunity with grouping by MONTH:Close Date and YEAR:Close date and Columns: COUNT and SUM

[northstrider]

Hello Tanya,
thank you for your answer!

There is still the problem, that reports (AdvancedPack) about type "Monthly Revenue (by User)" generates always "0 EUR" results. Probably I did something wrong. Maybe you'll see with just a glance what and can help... I did like this:

1. Entity manager > "Account": Creation of customer (with fields/records for name, address, contact info, description text, assigned user, a customer-id 6-digit number, other info...). Then import 7000 customers from Access as CSV-table (UTF-8). Works fine with cron-job.

2. Entity manager > Opportunity: Creation of sales-records (with fields for "type of order" like "direct", "on trade fair" or "re-order"... 7 conditions, the ordered amount in EUR, order date, delivery date, assigned user, customer-id 6-digit number). Then import of about 80000 records of sales/orders since year 2006 as CSV (UTF-8).

3. Entity manager > Relation "account" 1:n "opportunities". By customer-id and/or assigned user there should be a link between account and opportunity.

4. Create a Report: Monthly revenue/monthly income/sales by user or by customer-id, by date and so on. But nothing works, results are always "0". I cannot see any sums of sales amount in for example the last year and for a special user or all users who sold goods to the customers.

Can you tell me what I'm doing wrong?

Thank you so much for your support!!!

[tanya]

Account and Opportunities have a relationship 1:n.
If you want to save the relations, import in Account id the customer-id 6-digit number.
When you import the Opportunities, map the filed account id and the column customer-id 6-digit number.
In general for Opportunities Close Date field is required, so choose what column will be there. Or you can use own, but then remove default field (close date) from layouts.

Check imported records. Do they have good relations? (account, assigned user, amounts, dates)

Report works well, if import was correct and you set all needed fields.

[northstrider]

Thank you for your answer! Sounds like I am already close to the solution... at least I hope so...

I found that I need to use a different customer-id which is an integer (the id's are given automatically in Access and some are even negative, like "-214268695"). Is this a problem?

Must it be imported in account entity in a special default field or can I set up an own field, ie add field (integer) "customernumber"?

In Opportunities I then create a "customernumber" field too and map the Access-number to this field. Then it should be linked automatically?!

Sometimes I get an timeout error when larger requests are running. Maybe I should contact our provider about this (it is nothing about Espo, I guess?). And last I want to say, I really like your work and I hope to promote Espo with your AdvancedPack to more companies in future! You developers doing great and thank you for your support!

[tanya]

In EspoCRM id is varchar, so I think it's not a problem.
If you want to relate opportunties with accounts and you have only customernumber, better is if customernumber will be mapped with ID. If you have account name as well, you can relate by account name, you can use this field as relation
If your file is too big, you can run import in idle mode (will be done with cron, so no timeout)

[northstrider]

Hi Tanya!
Thank you for your answer!

Calculating with opportunities' revenues works now (with some detail problems, but in principle it works).

I still have a big problem though to get accounts linked with opportunities (several revenues in opportunities for a single customer in accounts).

I have fields for:
Account: "assignedUser" (a user name, i.e. "rr" with forename "Robert" and family name "Richter")
Account: "name" (the name of the customer company, i.e. "123Shop")
Account: an own created field in entity account for creating a unique customer id-number: "kundennummer" (is an integer), these are old assigned numbers from the old Access-database
Account: a second own created field with a number in entity account for creating a unique customer id-number: "cdlNumber" (is an integer), these are new assigned numbers used from now on

Opportunities: an own created field in entity account for creating a unique customer id-number: "kundennummer" (is an integer), these are old assigned numbers from the old Access-database
Opportunities: a second own created field with a number in entity account for creating a unique customer id-number: "cdlNumber" (is an integer), these are new assigned numbers used from now on
Opportunities: "account" (Link), name of the customer i.e. "123Shop"
Opportunities: "amount" with the order amount, part of the monthly revenue (ie. "100,- EUR")
Opportunities: "amountCurrency" with text-value "EUR", else Espo displays Dollars in the result, even though in import "EUR" is choosen...
Opportunities: "closeDate" (the order date)
Opportunities: "assignedUser" (Link), (a user name, i.e. "rr" with forename "Robert" and family name "Richter")

Assigned user, name of the customer and the two kind of numbers/id's of the customer should be linked between account and opportunity entities. But no matter what I try, I cannot make it work...

[tanya]

Hello,
there was a bug in import

https://github.com/espocrm/espocrm/commit/8f194e6d9ef1c70d3d46cbd8f19e0a034483c2f7

could you check in database the currency value?

[tanya]

And please remind me, what you want? - a list report for Opportunity allows you to select account fields

[northstrider]

Hello,

in import.php there is: $defaultCurrency = 'USD'; Can I just change it to 'EUR' and everything is fine?

For the big problem I have about linking between account and opportunity:

We need the connection between the 80.000 revenues summed up since 10 years in opportunity to the according 7.000 accounts of customers.

The fields in the entities that should be linked between accounts and opportunity are

- assigned user,
- name of the customer
- and the two kind of numbers/id's of the customer

from my above post. So that if clicking on a customers name in Opportunity it can show the account of the customer with its detailed information. And so that we can do different kind of reports about the revenue of the different assigned users, according to year and customer. Now there is an "Error 404 The url you requested can't be handled" when clikcing i.e. on a customer's name in Opportunity list. Same 404 if clicking on the "assigned user"-field. There is no 1:n link between account and opportunity.

[tanya]

"in import.php there is: $defaultCurrency = 'USD'; Can I just change it to 'EUR' and everything is fine?"
- better to copy the file content from github and set default currency in administration, if you didn't do it before.
the current data in database doesn't depend on this file, only if you will import in the feature.

As I understand, lets forget about report. We just need to import old data correctly at first. Yes?
Because, if the data is correct, and relations are set, we can build any report without problem.

Did you check imported opportunities? Do needed fields set right?

[northstrider]

Hi again, Tanya!

The fix about the currency works fine! I copied the file content from github and checked for the default currency in administration (was set correctly before). Thank you!

You are absolutely, complete and utterly right - let's forget about report in the moment. The import of the data is the cause of the problem, yes!
I see in the parts which are already working, that Espo is doing a great job. I like it! Yesterday evening, the connection between account and opportunities worked for the customer's company name! I've imported the customers name in "accountName" in Opportunity and "name" in Account. Then the link works!

There is still a problem that the assigning of the user does not work (clicking the link causes Error 404), as well as there is no relation for the unique number of the customers.

I tried to import into "Assigned User" (field "assignedUserId" in opportunity) the name of the Espo-users. But it does not work right. The user's name is in the field after import, but clicking causes 404...

We have in opportunity (some fields like delivery date I don't display here, because not important here):

Customer name like "123Shop" (imported into "AccountName") - Customer-Number (not sure where to import into... "ID"? or an own set entity-field as integer? I tried different variants, it imports but never links to the same number in Account) - Amount of money from an order (imported into "amount") - Order date (imported into "closeDate") - Assigned Espo-user like "Peter Pan" (imported into "assignedUserId")

We have in Account (some fields like address and so on don't displayed here):

Customer name like "123Shop" (imported into "name") - Assigned Espo-user like "Peter Pan" (imported into "assignedUser") - Customer number (own entity-field, one set for old Access-ID-numbers, one for new numbers set by us, all integers)

Does the "assignedUser"/"assignedUserId" has to be = "Peter Pan"? or the signing-in name, which is set when creating an user? For example "pp" for "Peter Pan".

[tanya]

"I tried to import into "Assigned User" (field "assignedUserId" in opportunity) the name of the Espo-users. But it does not work right. The user's name is in the field after import, but clicking causes 404...
Does the "assignedUser"/"assignedUserId" has to be = "Peter Pan"? or the signing-in name, which is set when creating an user? For example "pp" for "Peter Pan".
"

field Assigned User Name - has to be Peter Pan
if you use assignedUserId - it has to be and id field of user

Set only one field in import. assignedUserId is better if you have it, but assigned user name is good as well. But use only one.

The same with other relation. use {fieldName}Name field if you have Name only or {fieldName}Id field, if you have the id

If Customer number could be empty or it could be not unique, don't save it in ID field. Otherwise you can import your number as id. But the future customer's id will be not integer, but default string ID by EspoCRM. If you want to leave it as integer, use own field

[northstrider]

Aaaah! Thank you very much for your help! So the assignedUserId in Opportunities would be "Peter Pan" then? I'll try this afternoon the new import with "Peter Pan" in assignedUser (Account) and in assignedUserId (Opportunities) and report back to you (also for the other advices).

[tanya]

field Assigned User Name - has to be Peter Pan