[Ettienne Contributions: 0Level 1☆]

I have 3 searches on customers that have fields ID, Company Name, Discount

Search 1 uses Discount1 field, search 2 uses Discount2 field, search 3 uses Discount3 field.

Looking at the customer record there are 3 discount fields: Discount1, Discount2 and Discount3.

I need to produce a CSV file with:

Cust1, Cust1 Name, Disc1

Cust1, Cust1 Name, Disc2

Cust1, Cust1 Name, Disc3

Cust2, Cust2 Name, Disc1

Cust2, Cust2 Name, Disc2

Cust2, Cust2 Name, Disc3

etc.

It is easy to produce a search with Cust1, Cust1 Name, Disc1, Disc2, Disc3 but that is not what I need.

In SQL terms I could do a UNION query.

I'm sure there must be a smarter way than running 3 searches, exporting to Excel, combine results and then save as CSV.
This is a cached copy. Click here to see the original post.

[mwhite@hgyp.com Contributions: 0Level 1☆]

This can be done by:

1. Enable expressions so you can have multiple sets of criteria (i.e. each search).

2. User formula columns in results with Case statements instead of directly adding the desired field.

It will take a little tweaking, but as long as everything is looking at the same type of record, it's definitely possible. I'm happy to assist if needed.

[pcutler Contributions: 0Level 1☆]

Are the discount fields numeric fields or are they links to discount items? If they're links to discount items, then just add the following four results columns:

Customer ID> summary type = GROUPED

Customer Name > summary type = GROUPED

Discount Fields…Internal ID > summary type = GROUPED

Discount Fields…Base Price > summary type = GROUPED

[Ettienne Contributions: 0Level 1☆]

They are custom numeric fields, each customer can be assigned a discount rate for certain services.

[Author]

Posts