• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Crystal v8.5 fromula

Can you tell me how to create a formula for a field that based on the following information will bring back the name of the group?  For example, all the product types belong to a group called ERP and I would like to create a field for a group name that would look at the product types and batch everything together under the group name ERP.
{PROBSUMMARYM1.CATEGORY} in ["security", "software"] and
{PROBSUMMARYM1.SUBCATEGORY} in ["sap applications - production", "scheduled software"] and
{PROBSUMMARYM1.STATUS} <> "closed" and
{PROBSUMMARYM1.PRODUCT_TYPE} in ["erp_finance", "erp_finance_ap", "erp_hr", "erp_payroll", "erp_supply_chain_inv_mgmt", "erp_supply_chain_purchasing"]
0
SAW56
Asked:
SAW56
  • 10
  • 8
  • 2
1 Solution
 
frodomanCommented:
Can you assume that the group name is the first 3 characters of the product type as it is in your example or is that coincidence?  If you can't make that assumption then I guess you must have another table that contains a list of product types and what group they belong to?
0
 
SAW56Author Commented:
In most cases the Product Types do not have the same first 3 characters.  For example, we have a product type with the name Basis that belongs to the group Delivery.
0
 
frodomanCommented:
Okay, so how do you know that Basis belongs to the group Delivery?  Do you have a table that contains a cross-reference?  That would be by far the easiest way to solve this problem.  If you're intending to hard code this information it can be done but it will be a lot more work.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SAW56Author Commented:
Unfortunately, the groups Delivery and ERP do not reside in the system.  I need some type of formula that I can create that will look at the product types and based on what they are will group them based on the group names (Delivery, ERP, EAM, CCS, etc.)
0
 
SAW56Author Commented:
For example, the product types Basis, Bus_obj_ace belong to the Delivery group while the product types Erp_payroll, Erp_powerplant belong to the ERP group.  The product types are in the system but not the groups.
0
 
frodomanCommented:
The formula to return group names is very simple, just long.  You just need to keep adding all of the possible results to this formula and it will return ERP, Delivery, etc.






select {PROBSUMMARYM1.PRODUCT_TYPE} 
case "erp_finance", "erp_finance_ap", "erp_hr", "erp_payroll", "erp_supply_chain_inv_mgmt", "erp_supply_chain_purchasing":
   "ERP"
case "Basis":
   "Delivery"
default:
   "Unknown";

Open in new window

0
 
frodomanCommented:
Followup - I think the above will work in 8.5

If it doesn't, then I can help you with if..then statements but they'll be longer...
0
 
SAW56Author Commented:
Yes, could you please help.  I am getting an error message that "A number, currency amount, boolean, date, time, date-time, or string is expected here".
0
 
frodomanCommented:
Let's start very simply then:

if {PROBSUMMARYM1.PRODUCT_TYPE} = "erp_finance" or
   {PROBSUMMARYM1.PRODUCT_TYPE} = "erp_finance_ap" or
   {PROBSUMMARYM1.PRODUCT_TYPE} = "erp_hr" then
      "ERP"
else
      "Other";


Make sure that formula works and then we can add to it.
0
 
SAW56Author Commented:
Now I am getting the error that "The result of selection formula must be a boolean"
0
 
frodomanCommented:
Let's back up - where are you putting this formula?  It sounds like you are putting it into the selection criteria - this needs to be a new standalone formula (just create a new formula and name it anything you want).  After pasting in the formula drag the field onto your report.
0
 
SAW56Author Commented:
My mistake, your formula works but the Select formula (for the group names) does not.
0
 
SAW56Author Commented:
This is the information I need to incorporate into my formula (based on the formula provided earlier).  Can you somehow get it to work?
select {PROBSUMMARYM1.PRODUCT_TYPE}
case "erp_finance", "erp_finance_ap", "erp_hr", "erp_payroll", "erp_supply_chain_inv_mgmt", "erp_supply_chain_purchasing", "erp_powerplant":
   "ERP"
case "Basis", "bi", "isis-formatting", "isis-printing", "isis-online billing", "isis-messages", "isis-brochures", "isis-file transfer", "isis-workflow", "bus-obj-ace", "bus-obj-dqxi", "bus-obj-monthly update", "bus-obj-point of entry val", "bus-obj-us presort", "cronacle core software", "cronacle server agent",:
   "Delivery"
case "ccs_fica", "ccs_front_office", "ccs_back_office", "ccs_device_management",:
   "CCS"
case "eam_edeo", "eam_business_services", "eam_gen", "eam_managed_programs",:
   "EAM"
default:
   "Unknown";
0
 
frodomanCommented:
I need to understand what you are trying to do with this information.  You should be able to create a new formula using the text you've just posted and then put this formula on your report to see ERP, Delivery, CCS, EAM, or Unknown for every record.  Once that's done you can use this formula to sort, group, etc.

I apologize, but I'm just not clear on what it is you need to do other than what I've just stated?
0
 
SAW56Author Commented:
Everytime I go to create a new formula field using the formula provided I get the error that "A number, currency amount, boolean, date, time, date-time, or string is expected here" right after "cronacle server agent",:.  The cursor flashes between the comma and colon.  Does this information help?
0
 
mlmccCommented:
Assuming that is apaste for the formula
Get rid of the commas before the : at the end of each case.

mlmcc
0
 
frodomanCommented:
Agree - it's just a syntax error.  You want to *keep* the colon, just kill the comma that preceeds it.
0
 
SAW56Author Commented:
It worked...thank you.
0
 
mlmccCommented:
SAW56 : This should have been split or full credit to frodoman.  

Frodoman answered the original question.  My comment only resolved a syntax error when you implemented frodoman's answer.

I'll reopen the question so you can accept the correct comment and expert as the answer

You should use http:Q_23519473.html#a21883320 or  http:Q_23519473.html#a21884184

mlmcc
0
 
SAW56Author Commented:
Thank you....it worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 10
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now