Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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