Unique sorting requirement needed

[Craig_ST]

 First a bit of back story.  We photograph high school graduations, and when we are doing the family and individual component of this we first take a photo of an ID Tag that has a QR code and the students details.

In order to make these ID Tags, we create a Database with all the students in it for a particular school, and then select the layout that is formatted in the way we need, and we print 4 tags per sheet of paper, in numerical order, so page 1 has tags 1,2,3,4 page 2 has 5,6,7,8, etc. 

So, if for easy math,  if we have 400 students, this would create 100 pages with 4 tags each. 

We then have to guillotine that stack of 100 pages, 3 times, to create the 4 piles of tags, that now must be sorted into numerical order.

Here is the issue I want to solve.  

It would be much faster to do that physical sorting of the paper ID Tags, if I could sort the database so that Page 1 had tags 1, 101, 201, 301, Page, 2 had tags 2, 101, 202, 302, etc.  This would mean that when stacked vertically, the 4 stacks of tags that I have once the guillotining has been done would be 1-100, 101-200, 201-300, 301-400 and all I have to do is stack the 4 piles on top of one another.

So after all this waffling, how can I sort the records so that the order is 1, 101, 201, 301, 2, 102, 202, 302, 3, 103, 203, 303, etc to the last page that would be 100, 200, 300, 400?

I thought that making a new column that is a serial number assigned to jump 5 numbers each time would do it, but I can not seem to get that to work.

I hope I have made this clear, and really appreciate and am grateful for any assistance given.   

I am using FM 19.6.3.302 on Mac OS 12.7.1

 

Cheers

 

Craig

[Craig_ST]

Also we do a lot  of schools and the number of students is always different, so I would need a sorting solution that is dynamic in that it will adapt to the different numbers of records, it isn't going be 400 all the time like the example above.  

Cheers

Craig

Edited Thursday at 07:27 AM by Craig_ST

[comment]

You will need 2 fields for this:

(a) a SerialNumber field. This would be a Number field that auto-enters a serial number starting from 1 and incrementing by 1 (or, if you need an order that's different from record creation order, sort the records the way you want them and then use Replace Field Contents to populate this field);

(b) a PageNumber field. This would be an unstored Calculation field (result is Number) =

Let ( [
recordsPerPage = 4 ;
pageCount = Ceiling ( Get ( FoundCount ) / recordsPerPage ) 
] ;
Mod ( SerialNumber - 1 ; pageCount ) + 1
)

For printing, sort by PageNumber and by SerialNumber.

 

Edited Thursday at 08:20 AM by comment

[Craig_ST]

Hi

 

Thanks for your reply.  If I understand you correctly, I would need to manually type the serial numbers in the order that I want as incrementing them by 1 would just be the same as what they are already. Is there no way to create a calculation or script or what ever that will make the order of numbers in this serial field, and it does not have to be a serial number field I don't think - but to automatically make it order the files by 1, 101, 201, 301 or what ever the total number of records are so if there were 67 records, to pick a random number, it would still order them correctly.  

Also, does the number per page need to be specified, as the layout that I use will only print four per page anyways.

 

If I must manually order them, then doing it the old way and sorting the ID tags once printed and cut is probably faster, sadly.

 

My apologies if I have misunderstood, like, everything 😀

Edited Friday at 02:21 AM by Craig_ST

[comment]

5 hours ago, Craig_ST said:

I would need to manually type the serial numbers in the order that I want

Certainly not!!

But before going any further, let me understand better what you have now. Especially this part:

5 hours ago, Craig_ST said:

incrementing them by 1 would just be the same as what they are already.

So, in your example of 100 records, do you already have a field that numbers the records sequentially as 1, 2, 3 ... 100? If not, what do you use now to sort the records before printing them (in the less convenient order)? If yes, will you always print all the records in the entire table or do you sometimes print only a subset?

 

[Craig_ST]

Hi

 

Sorry, yes, the student records already have a unique ID that is assigned to them in the form of XXX1234, the XX variable being the schools ID code (we do a lot of schools) and then the 4 digits being the students ID.

and yes, we always print all the records, never a subset.

 

So for instance,  records 

XXX0001

XXX0002

XXX0003

XXX0004

...

XXX0100

 

would need to be sorted so that page 1 has XXX0001, XXX0026, XXX0052, XXX0077, and page 25 would have XXX0025, XXX0051, XXX0076, XXX0100

This would mean that when the 25 pages are guillotined, the 4 stacks I have are 1-25, 26-51, 52-76, 77-100

This must work with any number of records just using round numbers because it is easier. 

Thanks for your assistance, 

Cheers

Craig

 

 

[comment]

58 minutes ago, Craig_ST said:

the student records already have a unique ID that is assigned to them in the form of XXX1234, the XX variable being the schools ID code (we do a lot of schools) and then the 4 digits being the students ID.

I am afraid this part is still not clear enough to me. How exactly are these IDs generated and assigned? Will the first student (in the "real" order, not the order that you need for printing) always have an ID that ends with "0001" and then increments by 1 for the next (again, in the "real" order) student? And what exactly is the XX school ID code? Is it digits or letters or ... ?

 

[Craig_ST]

Hi,

 

Quote

How exactly are these IDs generated and assigned; And what exactly is the XX school ID code? Is it digits or letters or ... ?

The Student ID might be DDA0001, or any 3 letter, 4 number combo.  We assign these in an excel spreadsheet with the students names in alphabetical order and just grab the little handle on the cell and drag down to auto fill them.  

That excel sheet contains all other relevant data and is exported as a CSV file and then imported into our FM database. 

Quote

Will the first student (in the "real" order, not the order that you need for printing) always have an ID that ends with "0001"

 

Yes and  always alphabetical, so Adam Appleseed will always be XXX0001 and  Zorro  will always be XXXThe_Highest_Digit_Needed.

 

Thanks again for your assistance, and sorry I have not been clear enough.

Cheers

Craig

[comment]

Good, so that means you can use this field as is instead of the SerialNumber field in the calculation formula I posted earlier for the PageNumber field.

And since the creation order is the same as sorting by the ID field, you only need to sort by the PageNumber field before printing.
 

I suggest you try this to verify it works. I will try to add some explanations later to address the concerns you have expressed earlier.

 

Edited Saturday at 07:11 AM by comment