Getting error but data looks correct

Getting error but data looks correct

pzh20pzh20 Posts: 66Questions: 15Answers: 0
edited April 2016 in DataTables

I am getting the following error message;
DataTables warning: table id=Orders - Requested unknown parameter 'suppliers.SupplierName' for row 0.

However the data looks correct as follows

{
    "data": [{
            "DT_RowId": "row_2387",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "14/04/2016",
                "SupplierName": "Beillevaire UK Ltd",
                "Sum_ODAmount": "123.92",
                "orderInvoiceNumber": "160400458",
                "orderInvoiceAmount": "123.92",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2389",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "14/04/2016",
                "SupplierName": "Bread Ahead",
                "Sum_ODAmount": "400",
                "orderInvoiceNumber": "BO31036",
                "orderInvoiceAmount": "400",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2390",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "14/04/2016",
                "SupplierName": "Flour Power",
                "Sum_ODAmount": "44",
                "orderInvoiceNumber": "00099851",
                "orderInvoiceAmount": "44",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2386",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "14/04/2016",
                "SupplierName": "Gourmet Supplies Limited",
                "Sum_ODAmount": "123.99",
                "orderInvoiceNumber": "41837",
                "orderInvoiceAmount": "123.99",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2388",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "14/04/2016",
                "SupplierName": "The Celtic Bakers Ltd",
                "Sum_ODAmount": "18.4",
                "orderInvoiceNumber": "0000187537",
                "orderInvoiceAmount": "18.4",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2383",
            "orders": {
                "business_unit": "Warehouse",
                "OrderDate": "11/04/2016",
                "SupplierName": "DONOVAN BROS. LTD",
                "Sum_ODAmount": "109.11",
                "orderInvoiceNumber": null,
                "orderInvoiceAmount": "0",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2381",
            "orders": {
                "business_unit": "Camden Town",
                "OrderDate": "10/04/2016",
                "SupplierName": "Paella Amigo Ltd TA Amigo Food Group",
                "Sum_ODAmount": "71.85",
                "orderInvoiceNumber": null,
                "orderInvoiceAmount": "0",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2382",
            "orders": {
                "business_unit": "Warehouse",
                "OrderDate": "10/04/2016",
                "SupplierName": "BRAKES - Beverages & Consumables",
                "Sum_ODAmount": "150.94",
                "orderInvoiceNumber": "62076342",
                "orderInvoiceAmount": "150.93",
                "orderInvoiceVAT": "17.57"
            }
        }, {
            "DT_RowId": "row_2379",
            "orders": {
                "business_unit": "Warehouse",
                "OrderDate": "10/04/2016",
                "SupplierName": "INTLExpenses",
                "Sum_ODAmount": "1477.06",
                "orderInvoiceNumber": "WARE02379",
                "orderInvoiceAmount": "1477.06",
                "orderInvoiceVAT": "178.43"
            }
        }, {
            "DT_RowId": "row_2378",
            "orders": {
                "business_unit": "Warehouse",
                "OrderDate": "10/04/2016",
                "SupplierName": "Paella Amigo Ltd TA Amigo Food Group",
                "Sum_ODAmount": "95.8",
                "orderInvoiceNumber": null,
                "orderInvoiceAmount": "0",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2376",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "09/04/2016",
                "SupplierName": "Grovers of Borough Marrket Ltd",
                "Sum_ODAmount": "53",
                "orderInvoiceNumber": "9222",
                "orderInvoiceAmount": "53",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2375",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "09/04/2016",
                "SupplierName": "The Celtic Bakers Ltd",
                "Sum_ODAmount": "69",
                "orderInvoiceNumber": "0000187074",
                "orderInvoiceAmount": "69",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2373",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "08/04/2016",
                "SupplierName": "The Celtic Bakers Ltd",
                "Sum_ODAmount": "18.4",
                "orderInvoiceNumber": "0000186999",
                "orderInvoiceAmount": "18.4",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2374",
            "orders": {
                "business_unit": "Camden Town",
                "OrderDate": "08/04/2016",
                "SupplierName": "D.Jansen (UK) LTD.",
                "Sum_ODAmount": "84",
                "orderInvoiceNumber": "025",
                "orderInvoiceAmount": "84",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2368",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "07/04/2016",
                "SupplierName": "Flour Power",
                "Sum_ODAmount": "44",
                "orderInvoiceNumber": "00099535",
                "orderInvoiceAmount": "44",
                "orderInvoiceVAT": "0"
            }
        }, {
            "DT_RowId": "row_2367",
            "orders": {
                "business_unit": "Borough Market",
                "OrderDate": "07/04/2016",
                "SupplierName": "JetFreeze",
                "Sum_ODAmount": "638.18",
                "orderInvoiceNumber": "201603180",
                "orderInvoiceAmount": "636.18",
                "orderInvoiceVAT": "0"
            }
        }
    }],
"options": []
}

here's the output I am getting;

OrderDate:  SupplierName:   InvoiceNumber:  InvoiceAmount:  InvoiceVAT: Business Unit:  Order Amnt:
15/04/2016                                                          84                          0       
14/04/2016                               160400458          123.92                  0       
14/04/2016                               BO31036            400                         0       
14/04/2016                               00099851           44                          0       
14/04/2016                               41837                  123.99                  0       
14/04/2016                               0000187537          18.4                   0       
11/04/2016                                                           0                          0       
10/04/2016                               11732                   71.85                  0       
10/04/2016                               62076342            150.93                 17.57       
10/04/2016                               WARE02379   1477.06                    178.43      
Showing 1 to 10 of 1,426 entries       Previous  1  2  3  4  5  …  143   Next

Here's the JQuery

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: {
            //url: "suppliers.php",
            url: "fetchorders.asp",
            type: "POST",
        },
        table: "#Orders",
        fields: [ {
                label: "Order Date:",
                name: "orders.OrderDate",
                type: "date"
            },  {
                label: "Supplier name:",
                    name: "suppliers.SupplierName"
                },  {
                label: "Invoice No:",
                    name: "orders.orderInvoiceNumber"
            },  {
                label: "Invoice Amnt:",
                  name: "orders.orderInvoiceAmount"
            }, {
                label: "Invoice VAT:",
                name: "orders.orderInvoiceVAT"
            }, {
                label: "Business Unit:",
                name: "business_units.business_unit"
            }, {
                label: "Order Amnt:",
                name: "Sum_ODAmount"
            }
        ]

    } );
    
    var table = $('#Orders').DataTable( {
        dom: "Tfrtip",
        ajax: {
            //url: "suppliers.php",
            url: "fetchorders.asp?action=get&iDisplayLength=all",
            type: "POST",
        },
        columns: [
            //{ data: null, defaultContent: 'staffHours.staffHoursID', orderable: false },
            { data: "orders.OrderDate" },
            { data: "suppliers.SupplierName" },
            { data: "orders.orderInvoiceNumber" },
            { data: "orders.orderInvoiceAmount" },
            { data: "orders.orderInvoiceVAT" },
            { data: "business_units.business_unit" },
            { data: "Sum_ODAmount" }
        ],
        
        order: [ 1, 'desc' ],
        tableTools: {
            sRowSelect: "os",
            sRowSelector: 'td:first-child',
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor }
                //{ sExtends: "editor_remove", editor: editor }
            ]
        }
    } );
} );

Any ideas?

Regards
Pete

Edited - Syntax highlighting. Markdown details can be found in this guide.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    Hi Pete,

    The error looks like it is correct - there is no suppliers object in the objects loaded (I've formatted the code making it a bit easier to see).

    How are you creating that JSON?

    Allan

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0
    edited April 2016

    This is my first attempt at using multiple tables as inut to a DataTable, and maybe that's where I'm going wrong, as the inputs from the Orders Table are showing but not the other tables.

    This a Classic ASP implementation and I use VBScript to generate the JSON input as follows

    %>{"data":[<%
          if iTotalDisplayRecords > 0 and isarray(arrResults) then
                For i = LBound(arrResults, 2) To UBound(arrResults, 2)
                    if i > 0 then response.write "},"
                        %>{"DT_RowId":"row_<%=arrResults(0, i)%>","orders":{<%
                                 strThisDataPoint = ""
                                for z = 1 to (intNumCols -1)
                    strThisColName = toUnicode(colnames(z))
                    if len(toUnicode(arrResults(z, i))) = 0 then
                    strThisDataPoint = "null"
                    else
                    strThisDataPoint = """" & toUnicode(arrResults(z, i)) & """"
                    end if
                                if z > 1 then response.write ","
                    response.write """" & strThisColName & """" & ":" & strThisDataPoint
                         next
    
                         %>}<%
                       Next
                      Erase arrResults
         end if
    

    Is there something special I need in the input to specify it's from a different table. As you can see I'm generating the data .

    Regards and thanks
    Pete

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    Hi Pete,

    I'm afraid I can't help with the ASP aspect, but in order to use the DataTables configuration you've specified above, the returned row objects would need to contain and order object with OrderDate property (which you have), suppliers with SupplierName, business_units object with business_unit property and a Sum_ODAmount property (none of which you have).

    Having said that, your JSON object appears to contain all the information you need, just under the order object. So if you update your columns.data options to reference the data that is being returned, it should work as expected.

    Allan

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

    Allan,

    So for example, I'd need something like;

    "DT_RowId": "row_2387",
            "orders": {
                "OrderDate": "14/04/2016",
                "orderInvoiceNumber": "160400458",
                "orderInvoiceAmount": "123.92",
                "orderInvoiceVAT": "0"},
             "businessunit": {"business_unit": "Borough Market"},
             "suppliers":{"SupplierName": "Beillevaire UK Ltd"},
             "orderdetails":{"Sum_ODAmount": "123.92"}
    

    Regards
    Pete

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    If you used the columns.data options as you had it, yes,

    Allan

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

    Allan,

    As the SQL could come in with columns in any order e.g. orders.OrderDate, business_units.business_unit, orders.orderInvoiceVAT etc. do I need to re-arrange them or is it ok to end up with

        "DT_RowId": "row_2387",
            "orders": {
                "OrderDate": "14/04/2016"},
            "businessunit": {"business_unit": "Borough Market"},
            "orders": {
               "orderInvoiceNumber": "160400458",
               "orderInvoiceAmount": "123.92",
               "orderInvoiceVAT": "0"},
             "suppliers":{"SupplierName": "Beillevaire UK Ltd"},
             "orderdetails":{"Sum_ODAmount": "123.92"}
    

    ?

    Regards
    Pete

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    The order of the parameters in an object is irrelevant. That's one of the major advantages of using objects over arrays!

    Allan

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

    Sorry, my point was that there might be a reference to one table then another then the first again so I would end up with the above code. I suppose I could sort the fields by table name first, but wondered if the above would be ok having two or more references to Orders for example.

    Regards
    Pete

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    Answer ✓

    Oh I see - yes. I've just formatted the JSON and it makes the issue clearer.

    You are correct, you can't have the same key in an object twice or more. It needs to be unique. My understanding is that it is undefined what the Javascript engine will do when it finds a second key that already exists. V8 uses the new one. Some others might keep the original. None will combine them.

    Allan

  • pzh20pzh20 Posts: 66Questions: 15Answers: 0

    I managed to solve this with your help. Many thanks

This discussion has been closed.