Add Sort Order to purchase orders

[SBarnes]

Hi Danny,

Here's what I did for purchase orders that should fix your issue, if and when you need to use multiple fields.

Code: Select all

                      purchaseorder.SortOrders.Add(new JiwaFinancials.Jiwa.JiwaApplication.IJiwaNavigable.SortKey {
                               Description = "Creditor Name",
                               FieldName = "CR_Main.Name+PO_Main.OrderNo",
                            SortFieldValueMethod = delegate() { return purchaseorder.Creditor.Name + purchaseorder.OrderNo; }
                              });   

I would also add in hours, minutes and seconds as you suggested which should allow duplicates for the same day to work.

[DannyC]

Nah, something else must be going on.
Just doesn't want to play nice.

Code: Select all

      FieldName = "CONVERT(varchar(20),RE_Main.SlipDate,20)+RE_Main.SlipNo",
         SortFieldValueMethod = delegate() {return GRN.SlipDate.ToString("yyyyMMddhhmmss") + GRN.GRNNo; }


is what I would hope would work but doesn't. Might wait for Mike or Scott.

[Mike.Sheen]

Hi Danny,

You've got your string formats different for your SQL and your .NET which results in comparing apples with oranges.

You've got your SQL:

Code: Select all

FieldName = "CONVERT(varchar(20),RE_Main.SlipDate,20)+RE_Main.SlipNo"

Which sees data like this returned:

Code: Select all

2011-07-31 00:00:0032131

But then you've provided some different formatting for the .NET delegate SortFieldValueMethod:

Code: Select all

SortFieldValueMethod = delegate() {return GRN.SlipDate.ToString("yyyyMMddhhmmss") + GRN.GRNNo; }

Which returns:

Code: Select all

2011073100000032131

Which are going to cause you issues, as your formats are no longer the same. String comparisons are quite literal - those hyphens, colons and spaces included as a result of your SQL Convert using a format specifier mean your .NET string formatter which didn't include hyphens, colons or spaces is going to cause a mismatch.

Using your code, I found the following to work for me in demo data:

Code: Select all

SortFieldValueMethod = delegate() {return GRN.SlipDate.ToString("yyyy-MM-dd hh:mm:ss") + GRN.GRNNo; }

Try that and see how it works for you.

Running a SQL Profiler trace and seeing what query Jiwa issues with your plugin is a good way to identify such issues - it should make it fairly obvious why it wasn't working as you expected.

Mike

[Mike.Sheen]

Oh, and you might want to consider this:

Code: Select all

if(JiwaBusinessLogic is JiwaFinancials.Jiwa.JiwaInvReceival.Receival)

instead of:

Code: Select all

if(JiwaBusinessLogic.GetType() == typeof(JiwaFinancials.Jiwa.JiwaInvReceival.Receival))

Mike

[DannyC]

Cheers Mike. Never thought to use SQL Profiler!

So based on your tip, and then sussing out what the seed value is via Profiler, I found out what the correct syntax should be. I basically got rid of the SlipNo (not needed) and changed the hh to a H so I could get the hour in 24h format.

For those playing at home

Code: Select all

    public void Setup(JiwaFinancials.Jiwa.JiwaApplication.IJiwaBusinessLogic JiwaBusinessLogic, JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin)
    {
    if(JiwaBusinessLogic is JiwaFinancials.Jiwa.JiwaInvReceival.Receival)
      {
      JiwaFinancials.Jiwa.JiwaInvReceival.Receival GRN  =(JiwaFinancials.Jiwa.JiwaInvReceival.Receival) JiwaBusinessLogic;
      GRN.SortOrders.Insert(0, new JiwaFinancials.Jiwa.JiwaApplication.IJiwaNavigable.SortKey{
      //GRN.SortOrders.Add(new JiwaFinancials.Jiwa.JiwaApplication.IJiwaNavigable.SortKey{
         Description = "Slip Date",
         FieldName = "CONVERT(varchar(20),RE_Main.SlipDate,20)",
         SortFieldValueMethod = delegate() {return GRN.SlipDate.ToString("yyyy-MM-dd H:mm:ss"); }
         });
      GRN.BaseFindSQLQuery = "SELECT TOP 1 RE_Main.PackSlipID FROM RE_Main ";
       }
   }

Thanks for your help as always.

[Mike.Sheen]

I basically got rid of the SlipNo (not needed)

If you're satisfied there can possibly be no way two GRN's can be created with the same date given the temporal resolution attainable, then doing away with the SlipNo (or my preference would be RecID) - would work. Appending RecID all the same protects you from multiple GRN's with the same Date (I'm a defensive coder - consider imported GRN's with the same date!).

[DannyC]

If you're satisfied there can possibly be no way two GRN's can be created with the same date

Indeed. The difficulty I had was that there are plenty of GRNs on the same day. But it would be a pretty weird coincidence to see any GRN with the identical hh:mm:ss also.

This appears to be the SQL when the Previous button is clicked.

Code: Select all

exec sp_executesql N'SELECT TOP 1 RE_Main.PackSlipID FROM RE_Main  WHERE RE_Main.IN_LogicalID = @LogicalWarehouseResidingIn  AND   RE_Main.IN_LogicalID = ''ZZZZZZZZZZ0000000000'' AND  CONVERT(varchar(20),RE_Main.SlipDate,20) < @SeedValue ORDER BY CONVERT(varchar(20),RE_Main.SlipDate,20) DESC',N'@SeedValue varchar(19),@LogicalWarehouseResidingIn char(20)',@SeedValue='2018-06-04 15:16:55',@LogicalWarehouseResidingIn='ZZZZZZZZZZ0000000000'

It basically says (summarised)
WHERE CONVERT(varchar(20),RE_Main.SlipDate,20) < @SeedValue

I guess there may be a remote possibility that a few GRNs may have the exact same DateTime, so yeah you're right, it would skip those and just find the next one < @SeedValue.