Calculated Columns using IF and IN operators in Zoho Reports

Filed under #SoIDontForgetHowIDidThis, #Zoho

In Zoho Reports you can’t add lookup or standard columns to data sourced from Zoho CRM. You can however add calculated columns, which is great if you want to transform a list of usernames into a department for future filtering or grouping.

The syntax for this isn’t well documented, and it took a wee bit of fiddling to figure it out. We combine the IF function with the IN operator where:

IF is a function defined as:

if(expr1, expr2, expr3)

Returns expr2 if expr1 is true else it returns expr3

Example: if(5 > 10,100,50) = 50

IN is a list operator defined as: 

<expression1> in <expression2>

It’s also useful to know that lists are enclosed in round brackets (), string literals are enclosed in single quotes ‘’ and column references are  enclosed in double quotes “”.

E.g, to return the department name of a user based on the potential owner, the formula would be:

IF("Potential Owner" IN ('Anthony','Seth', 'Chris'), ‘Technology Infrastructure' , ‘Software Development’)

Which will result in a list similar too:

  • Seth – Technology Infrastructure
  • Bob – Software Development
  • Carol – Software Development
  • Anthony – Technology Infrastructure

For a more explicit result we can nest the IF function, which will be familiar to many Excel users:

IF("Potential Owner" IN ('Anthony','Seth', 'Chris'), 'Technology Infrastructure' , IF("Potential Owner" IN ('Phillip’,'Bob'), 'Software Development' , 'Other’))

Will result in:

  • Seth – Technology Infrastructure
  • Bob – Software Development
  • Carol – Other
  • Anthony – Technology Infrastructure

As Carol isn’t explicitly defined as being in the TI or SD departments, this is more correct.

How to engage a software development company – Part 4 – Understanding the software supplier’s process

Following on from what was covered in Parts 1, 2 and 3 we now I’ll look at understanding the software supplier’s process.

Understanding the supplier’s engagement process allows you to prepare in advance, quickly moving past the ‘sales’ and on to what they can actually do for you.  If you’re ready to get straight into it – these tips are for you.

The first thing a supplier is going to do is qualify you.  The supplier wants to know 3 things:

1. Do we have the capability to meet this client’s needs?

By capability I’m referring to the proficiency required to realise the project goals.  Typically suppliers work in ‘stacks’ – a number of technologies that can be leveraged to deliver a solution.  What they want to know is can they achieve your outcomes with their particular choice of technology stack?  A ‘no’ here is a show stopper, and it’s best to discover this as early as possible.

2. Do we have the capacity to meet this client’s needs?

Capacity is elastic in that suppliers’ resource schedules (and inversely, who they have available to work on a project at any given time) vary from month to month – and sometimes week to week due the fluctuating nature of project delivery.  The question being asked here is can we resource this within the client’s required timeframe? Often it’s a ‘probably’ – especially if there is room to be flexible as requirements can be phased and/or additional teams can be resourced to meet short timeframes. No one likes turning away work they can do, and suppliers will often work to design a compromise that works for both parties if capacity is an issue.

3. Has the client secured funding sufficient to meet their need?

Funding is a chicken and egg scenario because clients will often use a supplier’s estimate to inform their project budget, whereas a supplier will often tailor an approach to meet a specified budget. I like to have this conversation candidly and upfront.  I talk about our process, the various stages of a project and what sort of orders of magnitude other clients with similar requirements have had. This is a great approach that balances the supplier’s need to manage client expectations, with the client’s need to know what sort of money they will need to invest. 

The second thing a supplier will want to know is where you’re up to in the buying process, as this will determine what happens next. What happens next is about refining what is known and understood by both parties so as to achieve a reasonable degree of certainty about what can be delivered within a given time frame and for a specific budget.

Suppliers deal with hundreds of enquiries a year and need to make a judgement call about how much time they can afford to invest pursuing each lead. Preparing an estimate is a detailed exercise, often involving a number of people including business analysts, graphic designers, solution architects and the sales team.  Do not under-estimate the amount of work it takes to produce a quality, informed estimate. A supplier will need to stand by their estimate, so they will invest a large amount of effort in ensuring a reasonable amount of accuracy.  This underlines the importance of qualifying a budget, and where you’re at in the buying process – as it’s a reasonable expectation that if you ask a supplier to spend x days of work costing something for you, then it’s something you’re in a position to be able to go ahead with.

The next step is about determining to what level you and the supplier want to ‘de-risk’ your project. The level scale (from most to less risk) looks something like this:

Order of magnitude – Establishing a ball park can be achieved in a conversation and should be free.  It provides a rough indication of cost, and can span a range +/- 60% between a low and high figure.

Estimate – An estimate is more informed and typically accurate to +/- 30%.  Spending some money upfront to address perceived risks and confirm implementation assumptions can reduce this to a variance more palatable to you, and your suppliers, risk preferences.

Fixed price quotation – Producing a fixed price quotation requires the creation of one or more detailed specifications (akin to an engineer’s blueprints), which may be undertaken as an initial engagement. This is a substantiative piece of work and the outputs of such an engagement should be detailed enough that should you choose to, a second supplier can provide an estimate based on the created documentation.

Depending on the nature of your project and the number of unknowns – your supplier may require a level of certainty before agreeing to take on your project.  I’ve met with various responses to this – including righteous indignation and flat out refusal (it’s uncommon to pay for estimates in some industries). What’s often missed is that you’re not paying for an estimate – you’re paying for something to be discovered, defined and documented, which is a creative and collaborative process that you’re going to want to be involved in.

Don’t be afraid to spend a portion of your budget upfront to achieve this – as more becomes known, the supplier can be more accurate with their estimates, and you can control your level of investment and certainty with regards to deliverables.

So how can you leverage this information to start shortlisting, or engaging with a supplier?

  1. Be ready to go to market, this lets the supplier qualify you quickly – so you can take things to the next step, or cross a supplier off your list.
  2. Have a ball park discussion about budget. A ball park discussion will let you know if you’re talking to the right people or not.
  3. Let the supplier know where you are in the buying process.  It’s ok if you’re just gathering information at this point, but it’s important to let the supplier know that. Being up front about where you’re at is always appreciated and lets the supplier know how much information you need.
  4. Agree on what happens next. If the supplier makes your short list, I’d recommend a face to face meeting with the team that would be working on your project to discuss the level of detail required next.
  5. Remember, the supplier is evaluating you as a potential client as well.