Jump to content

Sorting Table by Data Values


-Lisa-

Recommended Posts

Anyone know of a way to sort rows in a table based on the value in the data?

 

For instance, data has five fields:

 

Field A is $100

Field B is $200

Field C is $10

Field D is $50

Field E is $0

 

Customer wants the data to appear in the table so that Field E is in row 1, Field C is Row 2, Field D is Row 3, Field A is Row 4, and Field B is Row 5. Each record in the data will include different monetary amounts for each field and each record will need these fields sorted lowest to highest.

 

Any thoughts?

 

This is so not the way I wanted to start learning tables! :mad:

Link to comment
Share on other sites

You could import the values into an array (strip the dollar sign) and then sort numerically:

var myValues = [Field("A").replace($,""), Field("B").replace($,""), Field("C").replace($,""), Field("D").replace($,""), Field("E").replace($,"")];
myValues.sort(function(a,b){return a-b});

 

Then, when building your table, just pull values from array rather than data (don't forget to add back the dollar signs).

Link to comment
Share on other sites

Thanks Eric!

 

I'm truly new to bulding tables but I have the table rule built already here:

 

var myTable = new FPTable
myTable.AddColumns(25903, 9107);
myTable.AddRows(3);
for (var row = 0; row < 3; row++)
{
for (var cell = 0; cell < 2; cell++)
{
myTable.Rows[row].Cells[cell].Font = "Georgia"; 
myTable.Rows[row].Cells[cell].TextColor = "Black";
myTable.Rows[row].Cells[cell].ShadeColor = "White"; 
myTable.Rows[row].Cells[cell].ShadePct = 100;
myTable.Rows[row].Cells[cell].SetBorders("Thin", "Black", "Top");
myTable.Rows[row].Cells[cell].HAlign = "Left";

}
}
// first cell has inverted colors
myTable.Rows[0].Cells[0].Font = "Georgia Bold"; 
myTable.Rows[0].Cells[1].Font = "Georgia Bold"; 
myTable.Rows[0].Cells[0].TextColor = "White";
myTable.Rows[0].Cells[0].ShadeColor = "Black"; 
myTable.Rows[0].Cells[1].TextColor = "White";
myTable.Rows[0].Cells[1].ShadeColor = "Black"; 
//Column 2 right aligns
myTable.Rows[0].Cells[1].HAlign = "Right"; 
myTable.Rows[1].Cells[1].HAlign = "Right"; 
myTable.Rows[2].Cells[1].HAlign = "Right"; 
 
//last cell has shaded colors and add'l border
myTable.Rows[2].Cells[0].TextColor = "Black";
myTable.Rows[2].Cells[0].ShadeColor = "Black"; 
myTable.Rows[2].Cells[0].ShadePct = "10"; 
myTable.Rows[2].Cells[1].TextColor = "Black";
myTable.Rows[2].Cells[1].ShadeColor = "Black"; 
myTable.Rows[2].Cells[1].ShadePct = "10"; 
myTable.Rows[2].Cells[0].SetBorders("Thin", "Black", "Bottom");
myTable.Rows[2].Cells[1].SetBorders("Thin", "Black", "Bottom");
myTable.Rows[0].SetContents("Earnings", "Martin's Paid");
myTable.Rows[1].SetContents("Salary Wages", Field("SalaryWages"));
myTable.Rows[2].SetContents("Gross Earnings", Field("GrossEarnings"));
return myTable.MakeTags();

 

To be honest, I did this just by reviewing Dan's post about the Table API and some additional feedback he provided me. So I'm not really sure what I need to change either in this rule or how to add your code in? :confused:

 

I wasn't expecting something that seemed so simple at the outset to morph into this nightmare...

Link to comment
Share on other sites

To be honest, I don't have much experience with tables either. But looking at your code, I presume the values from the array would go into the .SetContents() section(s) depending on how they are to be used. So instead of "Earnings" you might have "$" + myValues[1] to place the lowest value in the sorted array in that cell.

 

I would need to see what your final table is supposed to look like along with some sample data to sit down and figure out how to generate the specific table syntax to solve your scenario. My guess is that someone smarter than I will swoop in to rescue the both of us before that though!

Link to comment
Share on other sites

Thanks Eric.

 

Looks like no one wants to touch this one! ;)

 

I'm attaching a JPEG of what the final chart should look like along with some sample data with the hope that you or someone else can help me with this one. I've been staring at it all day and just can't figure out where to start...

 

 

 

TableImage.thumb.jpg.15afea1a2d524dd62aecfb54477eeb30.jpg

 

TestData.txt

Link to comment
Share on other sites

I'm confused about exactly what you're trying to do. You said in the original post, "Each record in the data will include different monetary amounts for each field and each record will need these fields sorted lowest to highest." But in your "JPEG of what the final chart should look like," I don't see how the data is sorted in any way. Also, the field names in your sample code don't match the field names in the data file you attached. So please clarify.
Link to comment
Share on other sites

If I understand correctly, you want to re-order the six values (and their associated "static" descriptions) so that the highest value is at the top and the lowest is at the bottom per record. Is that right? If so, do you also want to include all six descriptions, even if their value is $0?
Link to comment
Share on other sites

Dan,

 

In my original post, I simply used "Field A", "Field B", etc. however it also appears I didn't copy the full code the first time I posted it so let's try that again:

 

The code I already have written for the table is:

 

var myTable = new FPTable
myTable.AddColumns(25903, 9107);
myTable.AddRows(8);
for (var row = 0; row < 8; row++)
{
   for (var cell = 0; cell < 2; cell++)
   {
       myTable.Rows[row].Cells[cell].Font = "Georgia"; 
       myTable.Rows[row].Cells[cell].TextColor = "Black";
       myTable.Rows[row].Cells[cell].ShadeColor = "White"; 
       myTable.Rows[row].Cells[cell].ShadePct = 100;
       myTable.Rows[row].Cells[cell].SetBorders("Thin", "Black", "Top");
       myTable.Rows[row].Cells[cell].HAlign = "Left";

   }
}
// first cell has inverted colors
myTable.Rows[0].Cells[0].Font = "Georgia Bold"; 
myTable.Rows[0].Cells[1].Font = "Georgia Bold"; 
myTable.Rows[0].Cells[0].TextColor = "White";
myTable.Rows[0].Cells[0].ShadeColor = "Black"; 
myTable.Rows[0].Cells[1].TextColor = "White";
myTable.Rows[0].Cells[1].ShadeColor = "Black"; 

//Column 2 right aligns
myTable.Rows[0].Cells[1].HAlign = "Right"; 
myTable.Rows[1].Cells[1].HAlign = "Right"; 
myTable.Rows[2].Cells[1].HAlign = "Right"; 
myTable.Rows[3].Cells[1].HAlign = "Right"; 
myTable.Rows[4].Cells[1].HAlign = "Right"; 
myTable.Rows[5].Cells[1].HAlign = "Right"; 
myTable.Rows[6].Cells[1].HAlign = "Right";
myTable.Rows[7].Cells[1].HAlign = "Right";

//last cell has shaded colors and add'l border
myTable.Rows[7].Cells[0].TextColor = "Black";
myTable.Rows[7].Cells[0].ShadeColor = "Black"; 
myTable.Rows[7].Cells[0].ShadePct = "10"; 
myTable.Rows[7].Cells[1].TextColor = "Black";
myTable.Rows[7].Cells[1].ShadeColor = "Black"; 
myTable.Rows[7].Cells[1].ShadePct = "10"; 
myTable.Rows[7].Cells[0].SetBorders("Thin", "Black", "Bottom");
myTable.Rows[7].Cells[1].SetBorders("Thin", "Black", "Bottom");

//setting the rows in the table
myTable.Rows[0].SetContents("Earnings", "Martin's Paid");
myTable.Rows[1].SetContents("Shift Differential Pay Overtime", Field("ShiftDifferentialPayOvertime"));
myTable.Rows[2].SetContents("Shift Differential Pay", Field("ShiftDifferentialPay"));
myTable.Rows[3].SetContents("Overtime Pay", Field("OvertimePay"));
myTable.Rows[4].SetContents("Regular Wages", Field("RegularWages"));
myTable.Rows[5].SetContents("Bonus", Field("Bonus"));
myTable.Rows[6].SetContents("Weekend Pay", Field("WeekendPay"));
myTable.Rows[7].SetContents("Gross Earnings", Field("GrossEarnings"));
return myTable.MakeTags(); 

 

The JPEG I provided in my previous post was to show how the table needed to be formatted - which is exactly how the above code was written.

 

My issue is now the data (once again sample data is attached to this post) needs to be sorted so that the fields with the smallest monetary values appear first and the fields with the largest appear last. "Gross Earnings" will always be the final row. Previously, I set the rows in an order the customer specified. Now they need to change based on the values in each field.

 

So, for instance, in Record #1, I need to sort the fields so that "Shift Differential Pay Overtime" is first, "Shift Differential Pay" is second, "Overtime Pay" is third, "Bonus" is fourth, "Weekend Pay" is fifth, "Regular Wages" is sixth, and "Gross Earnings" is seventh.

 

In Record #2, I need to sort "Shift Differential Pay" first, "Bonus" second, "Weekend Pay" third, "Shift Differential Pay Overtime" fourth, "Regular Wages" fifth, "Overtime Pay" sixth, and "Gross Earnings" seventh.

 

...And so on and so forth for about 1,000 records.

 

I know the I posted above was successful for what I originally needed to do however I have no idea how to now change the sort order based on the value. And that's what brings me here.

TestData.txt

Link to comment
Share on other sites

Is this what you're trying to do?

 

// Your fields & remove the dollar signs
var a = Field("A").replace("$","");
var b = Field("A").replace("$","");
var c = Field("A").replace("$","");

// Create array with titles & monetary ammounts
var numbers = [["Shift Differential Pay Overtime",a],["Shift Differential Pay",b],["Overtime Pay",c]];


numbers = numbers.sort(function(a,b){return a[1]-b[1]});


var myTable = new FPTable
myTable.AddColumns(25903, 9107);
myTable.AddRows(3);
for (var row = 0; row < 3; row++)
{
for (var cell = 0; cell < 2; cell++)
{
myTable.Rows[row].Cells[cell].Font = "Georgia"; 
myTable.Rows[row].Cells[cell].TextColor = "Black";
myTable.Rows[row].Cells[cell].ShadeColor = "White"; 
myTable.Rows[row].Cells[cell].ShadePct = 100;
myTable.Rows[row].Cells[cell].SetBorders("Thin", "Black", "Top");
myTable.Rows[row].Cells[cell].HAlign = "Left";
myTable.Rows[row].SetContents(numbers[row][0],"$"+numbers[row][1]); //Populate your rows with their respective titles & add back the dollar sign

}
}
// first cell has inverted colors
myTable.Rows[0].Cells[0].Font = "Georgia Bold"; 
myTable.Rows[0].Cells[1].Font = "Georgia Bold"; 
myTable.Rows[0].Cells[0].TextColor = "White";
myTable.Rows[0].Cells[0].ShadeColor = "Black"; 
myTable.Rows[0].Cells[1].TextColor = "White";
myTable.Rows[0].Cells[1].ShadeColor = "Black"; 
//Column 2 right aligns
myTable.Rows[0].Cells[1].HAlign = "Right"; 
myTable.Rows[1].Cells[1].HAlign = "Right"; 
myTable.Rows[2].Cells[1].HAlign = "Right"; 

//last cell has shaded colors and add'l border
myTable.Rows[2].Cells[0].TextColor = "Black";
myTable.Rows[2].Cells[0].ShadeColor = "Black"; 
myTable.Rows[2].Cells[0].ShadePct = "10"; 
myTable.Rows[2].Cells[1].TextColor = "Black";
myTable.Rows[2].Cells[1].ShadeColor = "Black"; 
myTable.Rows[2].Cells[1].ShadePct = "10"; 
myTable.Rows[2].Cells[0].SetBorders("Thin", "Black", "Bottom");
myTable.Rows[2].Cells[1].SetBorders("Thin", "Black", "Bottom");


return myTable.MakeTags();

Link to comment
Share on other sites

Thanks Ste - I appreciate you taking a crack at this one.

 

I plugged your code into my template and changed "Field A", "Field B", etc. to the field names that match my data. But when I preview the records, it's sorting a few records lowest value to highest, but not all...

 

I'm looking at one record that has the following values:

 

Shift Differential Pay OT - $1,520.44

Shift Differential Pay Regular - $2.140.69

OT Pay - $41.00

 

So, this data should sort with OT Pay as the first row, Shift Differential Pay OT as the second row, and Shift Differential Pay Regular as the third row....But it previews just as I've typed it above.

Link to comment
Share on other sites

Ah, you know what? I bet it's the comma that's throwing off the sort. I revised the replace functions on the fields to remove dollar signs AND commas:

 


// Your fields & remove the dollar signs
var a = Field("A").replace(/[\$,]/g,"");
var b = Field("B").replace(/[\$,]/g,"");
var c = Field("C").replace(/[\$,]/g,"");

 

And altered the line of code that populates the table's rows to format the number back:

 

myTable.Rows[row].SetContents(numbers[row][0],"$" + FormatNumber("###,###,###.00",numbers[row][1])); //Populate your rows with their respective titles, add back the dollar sign, and re-format

 

That should work for you and get you back on track. Sorry about that!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...