Sort numerically but group by text?

[LaRetta]

Boy Scout camping trip:  Multiple poles are put up in various locations which are suitable for a campsite and the campsite poles are manually labelled onsite as 1, 2 , and sometimes as 10A, 10B, 10C.  This naming of the poles cannot be pre-determined and may be a mix of number and text but text always follows the number if it exists.  

 

Scouts are then manually assigned to these campsites in FileMaker (also cannot be pre-determined) and any number of scouts can be assigned to same campsite in any order.  Scouts put up tents with two scouts per tent.  And now the requirement:

 

1.  We need to group the scouts into two's for same campsite and print a sign with their name and the campsite on it.  

2.  Last scout for a campsite (if odd) will be on sign and in tent by himself.

3.  Order of assignment of the scout should be the order they are manually entered into database for same campsite (creation order).

4.  Signs should be printed in numerical order, i.e. 1, 2, 3, 8, 10A, 10B, 10C, 11, 12 ...

 

Of course you know where I'm going ... how can a break field sort numeric but group by text?  Script works and assigns them in two's but it breaks on the report when it groups them by cPrintSigns.

 

Simple files (fp7 and fmp12) attached showing it in action.  I could really use the help ... it seems simple but it keeps escaping me.   

Sorting.zip

[LaRetta]

By the way, I considered using form view with 2-row portal but I was hoping to skip another table occurrence just for this report plus I wanted to use this for two other similar situations where the grouping would assign teams of 8 and for table assignments of 4 (all scout stuff).  Also, this is in a solution which is not ( yet ) normalised and won't be until next version.

 

My cPrintSigns calc just fails to provide what I need; I've tried decimals and all kinds of combinations.  I suppose I could add a second part but I am bull-headed and I keep looking for the perfect, simple answer in the single calc or script instead.  

[comment]

4.  Signs should be printed in numerical order, i.e. 1, 2, 3, 8, 10A, 10B, 10C, 11, 12 ...

 

Is this really important? Because "10B" sure ain't a number... To sort them the way you want will cost you a calculation field, say cCampsiteNum =

GetAsNumber ( Campsite )

Now sort by cCampsiteNum and by Campsite and group by Campsite. I'm afraid I didn't quite understand the rest of the process - I think this would eliminate the need for your cPrintSigns field?

[rivet]

create a sort field:

 

Let ( [

 

_num = GetAsNumber ( campsite ) ;

_alpha = Substitute ( campsite ; _num ; "" ) ] ;

 

Right ( "000" & _num ; 3 ) & _alpha

 

)

[rivet]

part II - grouping campers two per tent

 

see who is odd or even in related campsite. 

create appropriate relationship

constrain report to just the odd numbered camper

Let ( [

_list = 
ExecuteSQL ( "
SELECT
id
FROM
thedata
WHERE
campsite = ?

" ; ", " ; "¶"; thedata::campsite ) ;

_pos = Left ( _list ; Position ( "¶" & _list ; "¶" & thedata::id ;1 ;1  ));
_row = PatternCount ( _pos ; "¶" ) +1;
_odd =  Mod(_row ;2)  ] ;

_odd

)

sorting v2.fmp12.zip

[LaRetta]

Hi Michael, thank you for assisting me! 

 

Unfortunately the sort order is very important to them (one gathering can have over 3,000 scouts). So following your suggestion, I created cCampsiteNum and then sorted ascending on both cCampsiteNum and Campsite. The leading part is set to Campsite.  However, the results aren't quite correct in that, for example the first one Campsite 7A, it groups William on same sign and it needs page break. That is why I added the Assignment and cPrintSigns so I could group and page break by it.

 

Switching from the report back to browse and returning to the other layout, you can see cPrintSigns has sorted right as you say but I need to page break it into two scouts now.

 

Hello Rivet, I appreciate you joining in!

 

Sort calc!  

 

Your calc produced same results as Michael's. I believe I wasn't clear that only two names for same campsite can be on a single sign.

 

Adding an & Assignment to the end of your calculation, and then using your calculation as the breakfield (leading part and also sort) did the trick!  

[rivet]

see my attachment

[comment]

I see what you mean. I didn't realize that you also needed a sub-summary heading for each pair. Once you put it like that - a sub-summary heading for each pair - the answer becomes obvious, doesn't it? Sort by cCampsiteNum and by Campsite and by Assignment; make your sub-summary part group by Assignment. IOW, let Filemaker do the work.

[LaRetta]

Wow, Michael, the answer didn't become obvious until I put it through some tests.  It seems I was missing a very important principle about summary parts ...

 

I thought that if I wanted Assignments grouped by Campsite, that I needed to either provide a leading Campsite part or include campsite into the field which IS in the leading part.  WRONG

 

I thought that grouping only on Assignment ( as leading part ) would group ALL assignments together.  WRONG (if sorted properly)

 

I did not realise that, if sorted first by other things (even if those other things are NOT a part) that the single Assignment part (which is LOWER in the sort group) would group ALSO by the other (HIGHER) sort fields first. 

 

If I create a Type field, assign Type to the records, and sort by Type then Assignment, it groups by Type and then Assignment!  A MIRACLE!!  And beautiful!  I never knew this!

 

So to condense the behaviour, might it be explained this way?  

 

A report will group by the field(s) defined in the leading sub-summary part(s) and any fields higher in the sort definition (than the leading part field sub-summary part ) even if those fields are not defined in the layout.  

 

EDITED the last theory paragraph.

[LaRetta]

Hi Rivet,

 

I appreciate the additional sample file and I assure you that I will give it proper study.  I actually considered using a temp table and loading it with ExecuteSQL() as well.  BTW, I love the tents!!

 

As is, Comment's solution is the leanest and fastest and I'll be going with that approach on this one but I adore seeing various techniques to solve the same problem because they all go into my tool kit for future!

 

Wow.  I cannot tell you both how wonderful it feels when something clicks and when I learn something really cool in FileMaker.  It is what keeps me going.  Thank you both so much!! 

[comment]

Yes, that is how it works - I am surprised at your surprise. I am attaching a file that shows this very clearly: note that when sorted by Bldg and Floor, you will see two sub-summaries for floor #3 - even though all those records form a contiguous sequence in the sort order.

 

BTW, do you actually need to record the tent assignments, or could you do a print & forget?

 

 

---

P.S. Grandma, why is your font so big?

Group.fp7.zip

[LaRetta]

Talk about reading my mind!  I was wondering about multi-user safe, alternate approaches (only virtual technique came to mind) but here's the thing ... I really want to stay away from the graph if possible.  

 

This solution is 17 years old (converted faithfully up through versions) and I would prefer avoiding graph impact because, not only are there over 800 table occurrences in a single entity tangle but I fear making a change in the graph might bring down the house since it has crashed and been recovered over 60 times through those years.  In fact, it has been reported that three developers fell into the graph and were never heard from again.  I have updated my Will accordingly.

 

BTW, do you actually need to record the tent assignments, or could you do a print & forget?

 

I would love to do a print & forget even if it meant entering the realm of TO-darkness to achieve it.   

 

I am attaching a file that shows this very clearly: note that when sorted by Bldg and Floor, you will see two sub-summaries for floor #3 - even though all those records form a contiguous sequence in the sort order.

 

Your Group.fp7 is perfect example of this natural ( by sort order ) sub-summary grouping.  It has opened another world of possibilities and lifted some needless constraints from my thinking.  Thank you for taking the time to present it, Michael.

 

P.S. Grandma, why is your font so big?

 

My Mac acted out and messed up the font so I selected it all and resized it but it was difficult for me to read so I increased it.  Now I see the problem ... I'll correct the size now.  It *is* too big, LOL.   I didn't have my contacts on at that time, ya see?  I didn't.   

[comment]

I would love to do a print & forget

 

Well, I was just thinking... If you had a layout that could only fit two records on a page, you wouldn't need the assignment field and the script that populates it. You could move your title into the header and use the sub-summary by Campsite merely to force a page break.

[LaRetta]

So break on Campsite, sizing the page for two = bingo!!  Absolutely beautiful in its simplicity. 

 

So instead of one ultra-flexible layout which can accommodate multiple script-assigned groups (4-roomie bunk beds, 8-player teams etc), create layout for 2-record natural break, 4-record natural break...  the cost of duplicating the layouts is far outweighed by the simplicity of process, increased speed and decreased point-of-failure (inherent to all scripts which touch data).

 

As you've reminded me in the past, layouts are inexpensive and they can be hidden in folders so they do not clutter and this is perfect example when *duplicating 2-3 more layouts (when the one-page report holds only a few records elements) is the absolute winner here.  

 

* Duplicating layouts is worthwhile when there are few elements or the elements rarely change otherwise keeping them in sync can raise their cost above their benefit.  Trying to keep element-laden, constantly-evolving, data-entry layouts in sync is like an annoying nat flying your face that never leaves.