| Author |
Message |
customreport
Newbie
Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
|
  
Topic: Limiting Field Occurance
Posted: 04 Mar 2011 at 2:17am |
|
I have a field that has two different strings associated with the primary record and it causes the record to repeat. How can I limit the occurance of the field per record. Example, I have a sales order report and one of the fields in the report has two different strings that are associated to the sales order #. The sales order repeats for each string value.
|
|
customreport
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 04 Mar 2011 at 3:37am |
How do you know which of the string values to use? Or do you want to concatenate them and show the record once with both values included?
-Dell
|
|
|
| IP Logged |
|
customreport
Newbie
Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
|
Posted: 04 Mar 2011 at 3:43am |
In the table there is a cf_name and cf_field. The cf_name string identifies whether it is status or rep group and the the cf_field string shows the status or rep group names. If I could concatanate them somehow or make array that would help. If I can get them to all show on a single line I can then limit the field to show what I want. The main thing is to not have the sales orders repeating it's making my report worthless and it was working until the rep group field was added to the system.
|
|
customreport
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 04 Mar 2011 at 4:17am |
Group by Order. Try something like the following formula:
StringVar RepGroup;
if (PreviousIsNull({order.orderID}) or
{order.orderID} <> previous({order.orderID})) then
RepGroup := {table.cf_field}
else
RepGroup := RepGroup + ', ' + {table.cf_field};
RepGroup
This will concatentate the values into a single value.
If you don't need order details, put all of your fields in either order group header or footer sections and suppress the details.
-Dell Edited by hilfy - 04 Mar 2011 at 4:18am
|
|
|
| IP Logged |
|
customreport
Newbie
Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
|
Posted: 04 Mar 2011 at 5:01am |
That made the report do something like this
Order # status
status, status
status, status, status
status, status, status, rep group
When it starts having status and rep group together is when the order is starting to repeat. If there is a formula that will filter out the orders with both repgroup and status then that might work, but then I'll need to format the field to only show the first string.
|
|
customreport
|
| IP Logged |
|
customreport
Newbie
Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
|
Posted: 04 Mar 2011 at 5:14am |
|
Can i do an if then statement like the one posted earlier saying if both rep group and status not repgroup?
|
|
customreport
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 04 Mar 2011 at 5:18am |
How are you grouping the report and what type of section is your data in?
-Dell
|
|
|
| IP Logged |
|
customreport
Newbie
Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
|
Posted: 04 Mar 2011 at 5:35am |
I'm grouping the report by date"daily" and my data is in the detail section because a single sales order can have multiple items and I need the items listed. I have something like this;
order.id-doc_num-customer.name-status-item-rep-qty-amt-po#-fob-ship_via-date
order.id-doc_num-customer.name-status-item-rep-qty-amt-po#-fob-ship_via-date
order.id-doc_num-customer.name-status-item-rep-qty-amt-po#-fob-ship_via-date
order.id-doc_num-customer.name-status-item-rep-qty-amt-po#-fob-ship_via-date
Total amount $
where the status is the cf.field. I need it like that because it lists all of the open order items for production, but now that the cf.field has rep group, say there is 4 lines with items like the above example, there will be 8 lines when the order has a status and a rep group and now the number of items for production is doubled and the amount is doubled.
P.S. rep in the report is sales person, not rep group. the rep group is included in the status field. Edited by customreport - 04 Mar 2011 at 5:36am
|
|
customreport
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 04 Mar 2011 at 7:10am |
I assume you're also sorting by order.id. Try this:
Go to the Section Expert. Click on the formula button to the right of the Suppress checkbox (do NOT put a check in the checkbox!) Enter something like the following:
next({order.id}) = {order.id}
This will suppress everything except the last record for each item in the order.
-Dell
|
|
|
| IP Logged |
|
customreport
Newbie
Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
|
Posted: 04 Mar 2011 at 7:30am |
|
That doesn't work because I need the orders to repeat for each item plus it still calculates the summary amount based on all the info including what is suppressed.
|
|
customreport
|
| IP Logged |
|
|
|
