Adding sum calc for json file data

Adding sum calc for json file data

JimBobUKIIJimBobUKII Posts: 13Questions: 1Answers: 0

Hey,
Sorry if this has been asked. I am fairly new and have tried searching online for the answer before posting.

I have a json file

[
  {
    "location": "Birmingham",
    "outlet": [
      {
        "outlet_code": 8228,
        "staff_number": 63,
        "holiday": [
          {
            "name": "All Spence",
            "details": [
              {
                "yos": 18,
                "age": 47,
                "department": "carpentry"
              }
            ],
            "id": 1971,
            "booked": 3,
            "remaining": 24
          },
          {
            "name": "Andrew Buchanon",
            "details": [
              {
                "yos": 8,
                "age": 34,
                "department": "design"
              }
            ],
            "id": 1931,
            "booked": 2,
            "remaining": 21
          },
          {
            "name": "Alison Brancho",
            "details": [
              {
                "yos": 7,
                "age": 32,
                "department": "human resources"
              }
            ],
            "id": 1864,
            "booked": 1,
            "remaining": 24
          }
        ]
      }
    ]
  },
  {
    "location": "Manchester",
    "outlet": [
      {
        "outlet_code": 7884,
        "staff_number": 13,
        "holiday": [
          {
            "name": "Geoff Pass",
            "details": [
              {
                "yos": 7,
                "age": 41,
                "department": "finance"
              }
            ],
            "id": 1344,
            "booked": 14,
            "remaining": 12
          },
          {
            "name": "Jan Gatehouse",
            "details": [
              {
                "yos": 7,
                "age": 32,
                "department": "finance"
              }
            ],
            "id": 1544,
            "booked": 2,
            "remaining": 25
          },
          {
            "name": "Lewis Spouse",
            "details": [
              {
                "yos": 1,
                "age": 41,
                "department": "goods in"
              }
            ],
            "id": 1132,
            "booked": 1,
            "remaining": 24
          }
        ]
      }
    ]
  }
]

I am looking to do the below within the table

location outlet_code staff_number holiday information
Birmingham 8228 63 (6) (69)
Manchester 7884 13 (17) (61)

holiday information col is worked out by sum of booked and remaining

I would also like the row to be highlighted if remaining is above 65

I hope someone could help and if possible give a little explanation too I would be grateful

TIA

Replies

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
  • JimBobUKIIJimBobUKII Posts: 13Questions: 1Answers: 0

    @colin Thank you so much!
    A few last things, how do I load the json data from a file?
    If 'remaining' total is above 65, how do i color the row?

    Thanks again

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    A few last things, how do I load the json data from a file?

    The typical way is with an Ajax request to the server. You can use ajax or jQuery ajax() to fetch the data form the server. The server will then respond with the file.

    If 'remaining' total is above 65, how do i color the row?

    Use columns.createdCell. The docs have an example.

    Kevin

  • JimBobUKIIJimBobUKII Posts: 13Questions: 1Answers: 0

    @kthorngren , I understand loading via ajax but as a noob I am unsure of how to fit this into the above @colin created!

    TIA

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    In my example the data is loaded from a variable using data. You can't simply load from a file, unless the browser is on the same machine, in which case you would do something like this. So generally you load the file via ajax as Kevin said.

    Colin

  • JimBobUKIIJimBobUKII Posts: 13Questions: 1Answers: 0

    @colin thank you for your help! I havent been able to sort this out! i was wondering if you could send over a demo?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    The best bet would be to look on StackOverflow as this is generic JS and not specific to DataTables,

    Colin

  • JimBobUKIIJimBobUKII Posts: 13Questions: 1Answers: 0

    @colin with your help, I have managed to do this now!
    Again, thank you so much!

  • JimBobUKIIJimBobUKII Posts: 13Questions: 1Answers: 0

    @colin I have one last thing to work out and I am sure you will be able to help with this one! using the data i irst provided, how can I display the table like

    location outlet_code name yos age
    Birmingham 8228 All Spence 18 47
    Birmingham 8228 Andrew Buchanon 8 34
    Birmingham 8228 Alison Brancho 7 32
    Manchester 7884 Geoff Pass 7 41
    Manchester 7884 Jan Gatehouse 7 32

    As you can see, i need to get location, outlet_code and store it somehow and pass it to the (what I would call) child object

    I have no idea on how to do this. Would you help me out?

    TIA

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    You'll need to restructure your data for that, either on the server or on the client, as DataTables wouldn't be able to merge the data from the two top-level arrays. You'll need to merge the outlets and store those two fields within the records.

    Colin

  • JimBobUKIIJimBobUKII Posts: 13Questions: 1Answers: 0

    I was beginning to think that myself!
    I will go back to dev team with an example of what I need.
    The problem I have is they are going to say the json file is going to be somewhat larger than currently but hey ho!

    Thanks again

This discussion has been closed.