Concatenate fields using Combinatorics

[lizzie]

Greetings!

Please help!

We assist with the email send for an events company and we have been supplied with a database from which we extract each race entrant's data and send them a personalised email.

Included in this data is whether or not the entrant has purchased a t-shirt for the race.

The client has supplied us with an Excel spreadsheet which includes separate fields for small, medium, large, x-large and xx-large t-shirts. The fields contain the number of t-shirts ordered by the entrant.

The extraction of data becomes more complicated as there are cases where one entrant will purchase multiple t-shirts of different sizes.

In the event of a race entrant purchasing more than 1 shirt, the client wants the email to read:

T-shirts: 1 x Small, 2 x Large, 3 x XX-Large

As far as I can see, to get the above data, one needs to use combinatorics to identify every possible combination of t-shirt purchases. My method will involve multiple "if statements" which identify those fields that are empty and those that are not; finally concatenating them into one field.

It would be easy to create an email which reads:

S T-Shirt: 1

M T-Shirt: 0

L T-Shirt: 2

XL T-Shirt: 0

XXL T-Shirt: 3

as this is simple data extraction... but alas, the client wants the data concatenated!

Does anyone know of a simpler (and faster) way to do this?

Any help would be greatly appreciated!

Thanks!

Lizzie

[iMarcW]

Aside from using the Case function instead of If for each shirt size, I don't see this as an onerous thing to have to do. I do it all the time.

I wrote the first line of the following by hand, then copied and pasted the remaining lines, just changing the field names and text labels each time. You should be able to use it by changing the field names for the sizes (two instances on each line) to whatever your field names are. Note that there are two spaces at the end of each text label to separate the sizes.

"T-Shirts: " &

Case ( Small ≥ 1 ; Small & " x Small " ; "" ) &

Case ( Medium ≥ 1 ; Medium & " x Medium " ; "" ) &

Case ( Large ≥ 1 ; Large & " x Large " ; "" ) &

Case ( XLarge ≥ 1 ; XLarge & " x X-Large " ; "" ) &

Case ( XXLarge ≥ 1 ; XXLarge & " x XX-Large " ; "" )

The trickier part would be checking for those where no t-shirts were ordered. It might be useful (if you needed to use the information elsewhere, otherwise it's better to use the Let function within the calculation) to create a calculated field that simply adds the above fields together, something like "TShirtsTotal". Then you would include the above inside a Case statement, like so, to avoid having to do the extra calculations if there are no shirts:

"T-Shirts: " & Case ( TShirtsTotal < 1 ; "None" ;

Case ( Small ≥ 1 ; Small & " x Small " ; "" ) &

Case ( Medium ≥ 1 ; Medium & " x Medium " ; "" ) &

Case ( Large ≥ 1 ; Large & " x Large " ; "" ) &

Case ( XLarge ≥ 1 ; XLarge & " x X-Large " ; "" ) &

Case ( XXLarge ≥ 1 ; XXLarge & " x XX-Large " ; "" )

)

HTH

Edited July 6, 2008 by Guest

[comment]

Case ( Small ≥ 1 ; Small & " x Small " ; "" )

Or simply:

Case ( Small ; Small & " x Small " )

[lizzie]

You made it sound so simple *blush*. Thank you!

I have used your calculation and it works beautifully.

Last question is how would one add comma's between the t-shirts where multiple t-shirts of various sizes are ordered? Just helps the reader else one gets:

T-Shirts: 20 x Small 36 x Medium 46 x Large 36 x X-Large 15 x XX-Large (as an example)...

[Søren Dyhr]

Let's take Comment's:

Case ( Small ; Small & " x Small, " )

Pretty straight forward! But the calc' gets a little redundant to look at which reveals a flaw in the data structure - the biggest problem here isn't yours since what you have done is stumbling near thinking fields instead of records, and to implement a normalized structure is counter intuitive.

The point is that are these qualities and quantities supposed to be broken out in a separate table, where unstored calc's then ought to wrok this way:

Case(GetAsBoolean(field);field & " x " & type)

These could be aggregated via:

Substitute(List(theCalc);"¶";", ")

Why think about it this way? Well reporting upon what you have sold is next to difficult with the legacy base's structure. But the idea of changing it could be dissuasive indeed, because it would involve the use of 4-5 TO's employing chained relations and even cartesian type of relations, due to the nature of portalized data which slides up on empties! Bruce Robertsson have a templet hidden somewhere supposingly under the search term "Survey" which deals with it.

--sd

[Søren Dyhr]

I have meanwhile toyed a little with the summary facilitation in what seems to be the same interface that you're after, a single detail is beyond what's available in your version, and it is fortunately a non recursive CF:

http://www.briandunning.com/cf/62

--sd

T-shirts.zip

[comment]

Having a TO per product is hardly "a normalized structure".

[Søren Dyhr]

I know Bruce's solution is better, but Boyce and Codd knew nothing about how Filemaker Inc. Would implement relations in the first place.

I would say that the choice of software mechanics is an entire different matter than whether a solution is normalized or not, But I would gladly hear your argument for what in particular makes in denormalized, and why??

--sd

[comment]

I don't see a real need here to normalize the data. It seems the input is:

The client has supplied us with an Excel spreadsheet which includes separate fields for small, medium, large, x-large and xx-large t-shirts.

and the desired output is:

we extract each race entrant's data and send them a personalised email.

I think the best way to achieve this is to leave the data the way it is.

However, IF the data was normalized, and IF there was a demand to produce a Browse mode summary of order's line items, and IF there was a chance of an order containing more than one line with the SAME product, I would summarize the portal using either Ugo's method or a custom function.

A left outer join (such as Bruce's file) would NOT be suitable here, because it produces a row per product - whether the product was ordered or not.

[Søren Dyhr]

You didn't answer my question, why is the tired table method de-normalizing due to the use of a TO for each product. We can easily that the solution scales badly, but rejecting it as de-normalization seems to me like eristic dialectics ... plucking deliberately in the task at hand to reject it, where what I originally raised was the lack of perspective with choosing fields over records, which likely makes summaries difficult.

Well the template shows a method, but the argument pretty clear here, you have the same set of data shown in two places where some sort of sync'ing are required - not normalized!

--sd

Untitled.zip

[comment]

You didn't answer my question, why is the tired table method de-normalizing

No, I didn't and I am not going to. I don't think it's necessary to explain why having 5 foreign key fields, one for each type, is not a good idea - certainly not to you. I don't really care which normalization form is violated here, but I'm sure you can look it up.

[Quartekoen]

how would one add comma's between the t-shirts where multiple t-shirts of various sizes are ordered?

Case ( Small ; Small & " x Small, " )

If this is for clients, then you'll probably be fussing over the little things, such as the fact that, if a Small is the only size shirt your client ordered, the list will end with a comma:

T-Shirts: 5 x Small,

At the same time, you can't put the comma at the beginning of each size, since the same issue could arise. I think the solution for this would be to put a few case statements in between.

Case( 

Small; Small & " x Small";

Small and (Medium or Large or X-Large or XX-Large); ", "; 

Medium; Medium & " x Medium";

)

With this, it will put the comma in between ONLY IF there is an order for both small and a larger size shirt. If there is no medium, but an X-Large, the comma will still be placed. Repeat for the other sizes.

[comment]

Repeat for the other sizes.

That would indeed require some combinatorics. Fortunately, there's a simpler solution:

"T-Shirts: " & 

Substitute ( 

List (

Case ( Small ; Small & " x Small" ) ;

Case ( Medium ; Medium & " x Medium" ) ;

Case ( Large ; Large & " x Large" ) ;

Case ( XLarge ; XLarge & " x X-Large" ) ; 

Case ( XXLarge ; XXLarge & " x XX-Large" ) 

) ; 

¶ ; ", " )

[iMarcW]

Haha... I knew you were going to ask about the commas. :

And I also knew that it was going to make everything more complicated. Adding a separator to each Case statement that wouldn't look out of place at the beginning or end, such as a "|" character, would be an easy way to do it, but as you can see, neither your client nor many of the people on this board like to take the easy way out. The clients just know what they want, and the forum denizens like the challenge!

The funny thing is, I was just reading a FileMaker Advisor article that describes the same method as comment's last post, and I thought of this thread. I don't think you can top that for simplicity. For those with access to the Advisor archives, the article is "Concatenate Conditionally without Logical Functions," Oct./Nov. 2007.

[LaRetta]

Yes indeed ... this is not the first time we have seen Comment's prior calculation. I believe the first time I saw him use it was in early 2006 or 2005 in various forms. Things come back around all the time ...

[LaRetta]

You didn't answer my question, why is the tired table method de-normalizing due to the use of a TO for each product.

Even I can answer this one ... because if the company adds a size they must call the Developer to change the structure. Bahhhh. :crazy2:

[Søren Dyhr]

I don't think it's necessary to explain why having 5 foreign key fields, one for each type, is not a good idea - certainly not to you

Well yes the summary made on a found set of orders, suffers a little here, well I twisted the approach a little requiring less foreign key fields, utilizing this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000128

(...again) Which preemptively here obviopusly produces blank records which only might find their use - not nessersarily having any.

The comprehensive scripting is of course giving the inadequacies of the relational structure away, but the template as such shows how to let the data stay in it's original place during the making of a summary report - which is another focus area with normalization.

--sd

test.zip

[Søren Dyhr]

Even I can answer this one ... because if the company adds a size they must call the Developer to change the structure. Bahhhh

True it's a practical/logistics problem - But you are then willing to let the users add any number of field then, risking that they won't be a part of the formatting calc' this thread was about? What are the odd's they consider this in a spreadsheet'ish mindset?

But neither have you yet attempted answering why it's de-normalization at all? Even if a flat'ish structure might suit the actual needs, can't you actually make an attempt to break attributes out into a new table a de-normalization??

--sd

[lizzie]

Greetings!

Wow! I didn't realise that my (what I thought was a) small and insignificant post could attract so many brilliant minds! LOL!

Thank you to you all for assisting with my learning curve!

Much appreciated!