Hey there, first I need to explain the story to understand, I have customized a lot the Opportunity module to fit my need.
I have in an opportunity every month (a field by month) to know when my client is purchasing something:
A client purchase things to a provider. An opportunity regrouped a year of purchase with a template name (made with workflow): PROVIDER - CLIENT - YEAR
So for example: NIKE - SNEAKERSHOP - 2019.
And in it I got all the month fields : January: 1842.42€, February: 1345€ etc…
For now its working great thanks to custom code and workflow, but now I’m wondering how to proceed the next step: I need the current year and the last year.
So, we are in 2019, I need 2019 AND 2018.
First Possibility :
If I follow my logic I will have an Opportunity a year (NIKE - SNEAKERSHOP - 2018, NIKE - SNEAKERSHOP - 2019). I will delete old opportunity with a workflow (here 2017’s opportunity).
Sec possibility :
The second possibility is to have one opportunity for 1 provider and 1 client with double month : CurrentJanuary / LastYearJanuary
Hide fields with a drop down : Current Year, Last Year.
So when current year is chosen, I hide every last year fields related.
And at the end of the year, make a workflow to pass every Current Year fields in the last year fields.
Since 2 day Im wondering whats it the best option. Since I’ve got like 4k client and like 50 provider.
I hope I understand you…
So you have a workflow that groups all your months together of the current year i.e. 2018 or 2019. You now want a workflow that will group together all the months from the last 12 months which includes both 2018 and 2019?
Like: you can run a workflow in March 2019 and it will give you all opportunities together from April 2018 - March 2018?
Or just that it takes all from 2018 and up to 2019 of the current month i.e. Jan 2018 - Dec 2018 + Jan 2019 - current month?
Hey Samus,
Thanks for helping me !
I’ll try to explain myself better:
I’ve got like 3000 Clients and about 50 providers.
A client buy thing to provider, every month I will have to import a CSV file with who buy where. -> That is my Opportunities module (See the screenshot).
So you have an opportunities by Client AND Provider AND Year filled with every months turnover. (for example : My-little-business - Amazon - 2019)
Not every client buy from every provider, but I need to know if the database and the CRM will handle it (3000 client * 50 providers * 2 (2 years : Current and last year.) = 300 000 lines)
This the the main activity of my business, every of my employees will have to get the information really quickly so its extremely important for me 
My main question is : For the database, whats better : More line (Possibility 1 of my first post) or more collumn (Possibility 2 of my first post) ?
I hope i’m clearer than my first post,
Bastien
Ahh I think I understand you.
So option 1: You create a new separate opportunity for 2018 and 2019 and delete 2017 so you’ll have lots of rows in the Database each an Opportunity
Or option 2: You create a new opportunity for 2018 and 2019 which contains max 24 fields for the months having lower rows but with at least 12 more fields in the database.
I’m sure there are other people on board who can provide examples of how they have utilise SuiteCRM to manage large amount of data but realistically 30000 records in the database is more than manageable. Depends of course on resources and concurrent users but still 30K should be fine if self-hosted and taking steps to index database on values if you are doing reports on said Opportunities.
I would suggest to keep the opportunities separate unless you really are worried about speed or are on limited hosting. Having them separate provide flexibility for the future if down the line you want three years showing instead.
Now does address all the user’s requirements? I assume you are going down the route of having lots of fields on the single Opportunity is because your CRM users want to have all that information on one page i.e. that opportunity without having to click through to a related record
Well that is really where solution 2 would be beneficial for simplicity, but doesn’t mean you can have that feature for solution 1. If you did go down solution 1 you can easily add custom code to show the account’s related opportunity for the previous year and list those fields on a new tab/panel.
If you did go down solution 2 how would the totals and importing work? Would users need to import all the previous year just so users can see data from last opportunity? I assume workflow could help out with that but perhaps you would want to reduce number of workflows and just call a custom function to show the information dynamically instead like suggested above.
Regardless of the solution I don’t see the amount of either rows or columns would have a large affect on you but really the surrounding ‘maintenance’ and execution of the feature itself i.e. workflows to update data, or custom code to dynamically display etc.
1 Like
A big thanks for your answer, you completely understand my question.
I’ll stay with Option 1. And yes, you are right, I will have all my opportunities when I go for an account, so my user will be able to see the opportunities of an account.
I also plan to make a report with a conditional field “account” to show every opportunity related to an account, with a link on every account detailview, that way I plan to automate the conditional fields by tacking the current account ID and and make the report automatically.
Last but not least, the main final objective is to get a little dashboard on my Wordpress website for my client, that way they will see how they spend their money.
The totals is done with some custom PHP, and saved to database with beans.
For the import, this is the next step, after choosing an option, so Im on it right now ahah. Im starting searching on internet how to make a cron to import updated opportinuties every month, I really dont know how I will do it cause I will need the opportinities ID to important the good number to the good opportunities.