AnsweredAssumed Answered

Want to build a categorizing and listing calculation

Question asked by goodsera2 on Dec 10, 2018
Latest reply on Dec 10, 2018 by goodsera2

Hi all,

 

Below are my tables. "OrderList" field in "Customers" table is a calculation field that I need help to build.

Screenshot 2018-12-10 03.58.53.jpeg

For each customer, I want to have a long string value calculated in "OrderList" field

1. Listing all "ProductName" records related to the customer in a single line, grouped and sorted by each "ProductType"

2. Having " " between "ProductName" records within the same "ProductType" group, and "; "between "ProductName" records from different "ProductType" groups

3. Printing the name of each "ProductType" at the end of each "ProductType" group before "; "

4. Hiding certain "ProductName" records whose "BundledStatus" records in "Invoices" table are "Yes"

5. Inserting each "BundleName" record at the end of an appropriate "ProductType" group based on its "ProductType" records in "Bundles" table, but before the name of each "ProductType" .

 

For example, if I have 3 tables below for CustomerID 1,

 

<Invoices>

CustomerIDProductIDBundledStatus
11Yes
12Yes
13
14
15

 

<Products>

ProductIDProductName
ProductType
1A2
2B2
3C1
4D3
5E2

<Bundles>

CustomerIDBundleNameProductType
1A/B2

 

I want "OrderList" to be "C 1; E A/B 2; D 3" by following this order : "C A B E D" -> "C; A B E; D" -> "C 1; A B E 2; D 3" -> "C 1; E 2; D 3" -> "C 1; E A/B 2; D 3".

 

I know how to use "List" function and "Substitute" function, but it seems I need more functions that I do not know.

This is probably easy to code in other programming languages, but I feel this would be very challenging to build in a Filemaker calculation field.

 

Please share any idea with me.

 

Thanks.

Outcomes