My Stuff

3946008 Newbie

Comments

  • Hello Kirk, Good day! I hope the following steps help you. I created a saved customer (or Member) search for this: 1. Please navigate to Reports > Saved Searches All Saved Searches > New 2. Click on Member link 3. Set the following under Criteria Tab > Standard Subtab: * System Notes Fields... > Field = Status * System…
  • Create a customer search, In the Results tab add the following fields.. Field = Name Summary Type = Group Field = Credit Card Internal Id Summary type = Count No need for Formula :) Thanks, Mon
  • Hello, Just trying to figure out what you are doing.. Are you guys referring to Setup> Sales> Sales Territories? Under Configure Rule, You can Apply a rule between two Zip codes. Not sure if this is what you guys want. Thanks, Mon
  • CASE WHEN {custcol_item_category} in ('Other' , 'License - Term' ) then {fxamount} WHEN {custcol_item_category} in ('Maintenance - Renewal' , 'Maintenance - New' ) AND ({revrecenddate} - {revrecstartdate} = 0) then '0' WHEN {custcol_item_category} = 'License - Perpetual' AND {revrectemplate.name} <> 'License Immediate'…
  • The THEN clause is a date and a string.. Try wrapping the to_date with a To_Char() like this - to_char(to_date(concat(to_char(next_day({custbody_ ready_fo r_approval},'Monday'),'MM/DD/YYYY'),'06:00:00'),'mm/dd/yyyy hh24:mi:ss')-{custbody_approval_date_} ) see if that works.. OR another way to test to know where the issue…
  • ok so I tried creating a custom field that sources it's value from a search (to get the latest case created date based on case type) but the search has restriction. it will not display the custom field that is sourcing from a search. IF you only use the formula case when {case.type}='VCI: Onsite Visit' OR {case.type}='VCI:…
  • Try this search.. Standard Criteria: Type is Sales Order Main Line is false Item is YOUR_ITEM Shipping Line is false Tax Line is false SUMMARY Criteria: SUMMARY TYPE = Maximum FIELD = Formula Numeric FORMULA = Max(Round({today}-{trandate})) keep(dense_rank last order by {trandate}) Formula (Numeric) is greater than or…
  • the formula should be Field: Formula (Date) Summary Type: Maximum Formula: case when {case.type}='VCI: Onsite Visit' OR {case.type}='VCI: Account Review' then {case.createddate} end You can't do an inline edit if you use Formula. Thanks, Mon
  • few things we need, to help us understand. What field is the customer's close adte? how do you identify Opps in relation to customer record? when will you know if the customer is a win-back vs new client? That will help people here to understand your process... Thanks, Mon
  • Ok do a slight modification in the Results tab of your search.. Instead of SUM, use MIN. The. You see the ORDER By COLUMN? Select date from there.. That should only pull the 7/30 PO. Thanks, Mon
  • the keep dense rank is in the resutl tab and summary tab? what's wrong with the result if you use the keep dense rank?
  • Time Search: The date critieria for the saved search is wide (all time since May 1), but I want to include a few columns to show records from the "current business week" Anyone know what the SQL expression is for "current business week"? try doing : case when to_char({today},'WW')=to_char({date},'WW') then 1 else 0 end…
  • this is the correct summary criteria Formula Numeric : max(case when {custrecord63} = {user.entityid}||' '||{user.altname} then 1 end)keep(dense_rank last order by {created}) set it equal to 1. I attached a screenshot for you. Depending on what your Assigned To field value looks like in the Results tab , you may modify…
  • I suggest create a custom item field then source it from a Saved Search that will check open POs. See screenshots below to give you an idea how to create the search and the custom item field. NextAvailablePO2-4 is how you create the saved search, you can tweak the criteria.. what is important there is the Result and the…
  • One more twist to this formula....is there anyway to set the (MIN) within the Formula: CASE WHEN {activity.status} = 'Scheduled' THEN {activity.date} ELSE NULL END The reason is we have 4 custom fields included in the search results and when I set the Summary Type to Group for the non-Formula Date fields we lose the…
  • thanks errol, by chaning it to SUM i got it to show me the lines that i want to only show, only problem is i cant make it so that only thaose that equal (0) show in the report. Do you know how i might do that add this in the summary criteria using MAX as the summary type then select Formula(numeric) : DECODE(COUNT(DISTINCT…
  • Ok so here is what im trying to do and wondering if im going down the right path. I have a report that pulls in a list of all transactions that has a line item that contains the name "TR-APT-" or "TR-SS-". Which works great gives me 16 transaction numbers but should only have 12 So there are 4 transactions that have more…
  • Hello, I'm trying to run a saved search with the Parent Company | Customer ID. When I place "Name" in my results, the results always return "Parent Company : Customer ID". Is there a way to get just the Customer ID or EntityID and not the full heirarchy? Warwick Hi Warwick, Try this .. Display Child/ Sub company instead of…
  • Thanks Mondray but still getting no data. I put the code in as you sugested but no luck. I even changed the summary type to Max / Sum / Count and all came back with no data. If i take the code out i get my list of 84 rows but soon as its added i get nothing. Ivan In your result what are the rows that should be equal to the…
  • Hello- I am trying to calculate a commission amount on a sales order if a customer has a checkbox (a custom field in the customer record) selected or not. I have the following case statement in the saved search and it always calculates to 0, even if the customer record has the checkbox selected. CASE when…
  • As usual, I am over thinking something...perhaps someone has a quick and easy answer? I am working on the following client-based saved search: CRITERIA: Stage is Client And ( Task : Created By is any of Person 1, Person 2 And Task : Date Created is within this fiscal year ) And ( Transaction : Type is Sales Order And…
  • Ok so i have read a lot of threads on here but nothing has helped in what i need and im not even sure its possible. So i have a saved search with the following criteria: Criteria = Type is Sales Order Region is EMEA Customer Catergory is End User Results = Number (Summary Type - Group) Name (Summary Type - Group) Line ID…
  • Checkout the other thread, I posted something in there. Regards, Mon
  • So here is what i added to the summary tab under criteria but getting no results. Case when Count({LineID})= Count ( CASE WHEN {item} LIKE 'TR%' THEN 1 ELSE 0 END ) then 1 else 0 end Not sure if the entire formual was meant or just the case to look for the item? Thanks, Ivan Use SUM for your formula since you are returning…
  • Anyone doing anything like this? it still shows "01" in mine if I use Formula(text) = to_char(to_date('01/01/2014','MM/DD/YYYY'),'IW').. but if I use Formula(Numberic) = to_number(to_char(to_date('01/01/2014','MM/DD/YYYY'),'IW')) it shows '1'. I know it's a normal behavior is NS to remove leading or trailing zeros in…
  • Any idea why this formula throws up an "ERROR: Invalid Expression" error? CASE WHEN {type.id} = 'RtnAuth' THEN 'Yes' END type.id works fine as a result field, and brings back RtnAuth. could be that it's not exposed in the criteria field. {Type.id} is the same as {Type}, the only difference is that it's the value of…
  • I am trying to pull up a search that reports on the following: Average Customer Spending/Sales in the month The challenges I am facing are numerous, which is ironic - because it seems like such a simple search. First of all, I only want a certain subset of records to pull up - and lines within those transactions. I am…
  • I can add a group by internal id and count by member name -- that will identify anything greater than one. Is there a way to write a formula in the criteria to only show results with a count greater than 1? You will need to use the Summary criteria in the Criteria tab. select COUNT in the Summary type then Select the…
  • My problem is I need to classify sales orders by product line, and the mix of various product skews on a given order determine that product line. I can't go by the Class as indicated in the item record as we have products of the same Class that span different product lines. For some orders I can go by certain fields in the…