We want to find out how many days an Opportunity in SugarCRM has been open, using a calculated custom integer field in SugarCRM Studio. I’m assuming you’ve already created a new custom integer field, likely called something similar to “Days open”.
The sugar logic to calculate the difference between two dates is this:
and the sugar logic to do an if then else is:
The first test we’ll do is if the sales stage is “Closed Lost” or “Closed Won”, if it’s true, we’ll calculate the days between the create date and the expected close date. If the sales stage is something other than “Closed Lost” or “Closed Won” then we’ll calculate the days between the create date and today.
This makes our pseudo sugar logic formula look like this:
ifElse( or( equal($sales_stage,“Closed Lost”), equal($sales_stage,”Closed Won”) ), calc_days_create_to_expected_close_date, calc_days_create_to_today )
Which, when we update it with the real sugar logic parts becomes this:
ifElse( or( equal($sales_stage,"Closed Lost"), equal($sales_stage,"Closed Won") ), abs( subtract( daysUntil($date_entered), daysUntil($date_closed) ) ), abs( subtract( daysUntil($date_entered), daysUntil( today() ) ) ) )
Now, removing all the spaces we get this:
ifElse(or(equal($sales_stage,"Closed Lost"),equal($sales_stage,"Closed Won")),abs(subtract(daysUntil($date_entered),daysUntil($date_closed))),abs(subtract(daysUntil($date_entered),daysUntil(today()))))
This post was written in response to a question on LinkedIn.
Quick pointer to Jason Fried talking about why work doesn’t happen at work (Youtube link).
Quick pointer to Seth Godin’s blog from August 2010 about “The secret of the Roush effect“.
Scott Hanselman, all round smart guy has for the last decade, compiled a list of tools and utilities that he finds useful.
Spam in Australia:
Under the Spam Act, it is illegal for unsolicited commercial electronic messages that have an Australian link to be sent, or cause to be sent. A message has an Australian link if it originates or was commissioned in Australia, or originates overseas but has been sent to an address accessed in Australia.
Yes! As Jeff says “…stop writing books about Alfresco Explorer“.
“Share” the love. :)
Having bought more than a few Alfresco books, please for the love of all that’s good, don’t write more about Explorer. We’re done with it. Move on. Please.
Dave Winer on the ‘pulse of Apple’ . Makes sense. Makes me think “what is the pulse of company I work for?”.
Michael Sliwinski has relaunched the Productive Magazine . Loving the new format, and excited to hear that it’s now going to be regular each month. I can recommend you go get it, I’ve learnt something from each edition so far.
- A great metaphor, using cups of water, that explains Google, Apple, Microsoft, Amazon and the dance between them.
- There is no private cloud, no public cloud, just hybrid, and 68% of customers will change vendor.
- Good explanation of a ‘partitioned’ approach and an ‘iterative’ approach using dice to help explain the concepts. Read this to understand how to reduce risk in your project.
- New version of Process Manager for SugarCRM is about to be released. And the release notes for SugarCRM 6.5.16.
- Hold onto your hat and sit down, here is an interesting cardboard chair and a chair for gamers (both youtube links).
- Why PowerBI use Talend.
- An explaination of the Four types of Enterprise Architecture.
- Fanboi’s step aside, the sheer number of iPads in education is impressive.
- Documentation on how to map an Alfresco space to a drive.
- Alfresco and Client WebDAV.
- Geeky podcast. Brett Terpstra in Systematic. OS X and iOS oriented, productivity geek heaven.
Off-topic, well, on topic, or both maybe: I’m finding it useful to write these ‘open loops’ as it’s just a bunch of links that I want to come back to, without losing them. Quite frankly it’s easier to find them here on my blog than anywhere else. I am the intended reader of these posts, but do let me know if your find them useful at all.
I’ve got a few things I don’t want to lose track off, open loops you could say. So rather than keeping my browser tabs open, I’m going to simply put the links here:
- Change the way you read blog posts
- 66 One sentence tips for bloggers
- A $10 digital microscope
- How to use Siri on iOS 7 (list of commands)
- Google Fonts
That’s all for now :)
Steven Pressfield taught us the secret of real writers:
There’s a secret that real writers know that wanna be writers don’t, and the secret is this: It’s not the writing part that’s hard. What’s hard is sitting down to write. What keeps us from sitting down is resistance.
Working with SugarCRM (Pro, OnDemand) last week and discovered a wonderful thing!
SugarCRM has been designed to import record ID numbers from other systems. So long as the id numbers you’re importing are less than 36 characters, and are globally unique (not just unique to the entity, i.e. contacts, notes, accounts) you’re in business, you can simply import the legacy record ID into the Sugar ID field (see below on how to make it globally unique).
This is a great thing. Before knowing this, and with other systems, you need to create a custom field in each entity to store the legacy ID number, import the data into that entity, then export it with the new system’s ID number. You can then match up this new ID number with other data so that the relationships get maintained. This is discovery is for me a wonderful thing, others already know this as it was designed this way, but this will save me a lot of work.
One of the tips given to me by one of the SugarCRM support people was that if the ID isn’t globally unique, then simply suffix it (or prefix it) with something unique for that entity. For example, if Accounts and Contacts both have an ID of “ABCD-1234” you could suffix all the Accounts record ID’s with “_Acct” and the Contact record ID’s with “_Cont”. This effectively makes them globally unique. Do the same for any ID’s in other entities, for example, in the Notes table, any Contact ID’s would get suffixed the same way with “_Cont”.
Account Records Before:
- ID, AccountName:
- ABCD-1234, Doe Exports
- ABCD-4321, Joes Toes
Contact Records Before:
- ID, ContactName
- ABCD-1234, Jane Doe
- ABCD-4321, Joey Blowy
As you can see above, the ID’s are unique to the entity but not globally unique (and we do want them globally unique). By suffixing the ID’s we can make them globally unique as you see below:
Account Records After:
- ID, AccountName
- ABCD-1234_Acct, Doe Exports
- ABCD-4321_Acct, Joes Toes
Contact Records After:
- ID, ContactName
- ABCD-1234_Cont, Jane Doe
- ABCD-4321_Cont, Joey Blowy
This really does make it much easier import lots of data and keep the relationships between pieces of information. For example, Notes would then look like this:
- ID, RelatedContact, RelatedAccount, Note
- 123_Note, ABCD-1234_Cont, ABCD-1234_Acct, “Spoke with Jane re recent purchase.”
- 456_Note, ABCD-4321_Cont, ABCD-4321_Acct, “Joey rang, wants to double his order.”
If only it were this easy importing into other CRM’s. Let me know if you have any questions about importing data into SugarCRM.
I am here because of my choices, my decisions.
Because of my actions and my inactions.
Like it, or like it not.
That is the why.
You could accurately say “I chose to be here”.
The choices ahead, they will determine where I go.
Where are you?
And where are you going?
Are you choices leading you where you want to go?
AddThis is a browser extension. It’ll allow you to easily share content on multiple networks.
The 11.11.11 project asks you to take a photo of whatever you’re doing at 11 minutes past 11 on the 11th November 2011…then contribute your pic to ABC Open.