New chart or portal?

[singlecoil]

Hello all,

I have a table with over 2000 records and I need to be able to view all of them on a single screen or sheet of paper when printed.  Each record is either a part of one group or one other and I want to be able to show which group each record is a part of.  I've attached what I hope to achieve.  The example has 40 records per row.  I can use conditional formatting to make each record either one color or another color, but I'm not sure how to get all the records to display like this.  I though of creating a table with 40 fields per record and assigning each field a binary value.  The first record in that table will show values 1-40, the second record would show values 41-80, etc.  I would write a script to assign values to each field using set field by name.  I could then display that table using list view, but that seems pretty cumbersome.

Any thoughts?

Thanks!

 

[comment]

58 minutes ago, singlecoil said:

I have a table with over 2000 records

Is the number of records fixed, or at least limited to some maximum? Also, do they need to appear in any particular order?

Consider to tell us more about what these records represent in real life, how often (if at all) they change, etc. This will help us suggest the optimal solution.

 

Edited January 24, 2017 by comment

[singlecoil]

The number of records is fixed.

They need to appear in order from 1-2400.

So each record represents an individual.  I have one table with people from one group, and another table with people from another group.  I then merge the two tables into a third table that contains all the records of each table.  The two groups are merged into the third table according to different weighting factors that the user can specify.   Consider it two companies merging and the employees need to be merged into a final ranked roster.  The point of the above chart is so that the user can easily see how the merged groups are distributed. Who ends up at number 1, who is at the end, and who is in between.  Currently I have them listed in a long list that you can scroll through, but it was pointed out to me that having the above would be more useful.  I plan on making it so you can click on one of the boxes and be taken to that record, or at least get some basic info on that record with a mouse over box.

[comment]

14 minutes ago, singlecoil said:

I plan on making it so you can click on one of the boxes and be taken to that record,

That limits the number of options significantly. I would suggest placing 40 portals, of 60 rows each, side-by-side and making each portal start at a different row (1, 61, 121 etc.). The relationship, using the x relational operator, could be a self-join.

By default, the records will be shown in creation order: down first, then across. This can be changed, but I am afraid you did not provide enough detail for me to suggest anything.

Don't expect this to win any speed records, no matter what technique you end up using.

 

18 minutes ago, singlecoil said:

I have one table with people from one group, and another table with people from another group.

Unrelated to the question, but this doesn't seem like a good idea.

[singlecoil]

You know what?  I've got it figured out.

I made 50 global repeating fields with 50 (instead of the 40 in the example picture) repetitions each.  Instead of making them calculation fields, I just made them text fields.  They I wrote a script to populate the fields and repetitions by using set field by name, and using variables to represent the different row numbers and repetitions inside a couple of loops.  Works fast.

Then conditional formatting to get the colors.

Thanks!

[comment]

1 hour ago, singlecoil said:

I made 50 global repeating fields with 50 (instead of the 40 in the example picture) repetitions each. 

Why not simply one repeating field, with 2400 (or 2500) repetitions?

The reason why I not suggested this is what I quoted above: you will find it quite difficult to click on a repetition and have it take you to the original record.

[singlecoil]

5 minutes ago, comment said:

Why not simply one repeating field, with 2400 (or 2500) repetitions?

The reason why I not suggested this is what I quoted above: you will find it quite difficult to click on a repetition and have it take you to the original record.

How would you get a repeating field with 2500 repetitions to wrap where you want it to on the layout?  My script writes the data from 4 fields from each record into each repetition of my display fields.  The data is separated by carriage returns.  Then I use middlevalues to display the 4 pieces of data as a tooltip when the mouse is hovered over each repetition.  Kinda slick.  I don't really need it to take me to the record, just provide some basic info of who represents that dot on the chart (name, age, date of hire).  I could probably store the record id as a separate value and have a button that takes me to that via middlevalues.

[comment]

45 minutes ago, singlecoil said:

How would you get a repeating field with 2500 repetitions to wrap where you want it to on the layout?

I would place 50 instances of the field on the layout and set the first instance to show repetitions 1 to 40, the second instance to show 41 to 80, and so on.

 

48 minutes ago, singlecoil said:

I could probably store the record id as a separate value and have a button that takes me to that via middlevalues.

I think you will find it difficult to let the button know which repetition was clicked. Unless you plan to define 2500 individual buttons.

[singlecoil]

12 hours ago, comment said:

I would place 50 instances of the field on the layout and set the first instance to show repetitions 1 to 40, the second instance to show 41 to 80, and so on.

 

I like it.  That is a lot easier to code the script to populate it, and might be overall a little faster, thanks.