Strategy for Complex data transformation

Bryan

Hello fellow Xano enthusiasts!

I've got an interesting challenge and am open to some ideas on how best to tackle it.

I've got an array of invoice data. Each of these invoices have been fully paid. I am trying to work out the number of days between the invoice being issued (being the invoice date) and the invoice being fully paid.

Here's the code excerpt below

{

"Invoices": [{

"Contact": {

"Name": "City Agency"

},

"Date": "\/Date(1518685950940+0000)\/",

"DueDate": "\/Date(1518685950940+0000)\/",

"Status": "PAID",

"SubTotal": "1000.00",

"TotalTax": "0.00",

"Total": "1000.00",

"UpdatedDateUTC": "\/Date(1518685950940+0000)\/",

"CurrencyCode": "NZD",

"InvoiceID": "243216c5-369e-4056-ac67-05388f86dc81",

"InvoiceNumber": "OIT00546",

"Payments": [{

"Date": "\/Date(1518685950940+0000)\/",

"Amount": "500.00",

"PaymentID": "0d666415-cf77-43fa-80c7-56775591d426"

},

{

"Date": "\/Date(1518686470940+0000)\/",

"Amount": "500.00",

"PaymentID": "0d666415-cf77-43fa-80c7-56132191d426"

}

],

"AmountDue": "0.00",

"AmountPaid": "1000.00",

"AmountCredited": "0.00"

}]

}

This JSON doesn't specifically provide a DatePaid field, so I have to work it out by iterating over the Payments array, and extracting the date timestamp, and working out of all the timestamps which is the latest one (which would indicate that was the date the invoice was fully paid).

I'd take that timestamp and minus with the "Date" timestamp and that should in theory tell me how many days it took to pay.

The question is how or what would be the best way to go about this. Bear in mind I'm showing one invoice of a possible x amount of invoices for the same client. So would have to iterate through all those invoices and get the number of days for payment for each of them.

Some things I can't quite work out are, how do I determine which is the largest timestamp in an array?

Is there a way to simplify this process? Right now as I'm sketching the process for this, it feels like I've got 15steps to do, to get what I'm after. Any specific functions I should check out?

Thanks all! I'm always open to learning more about the Xano stack! I've always just stuck to the simple functions, but things like this make me wonder if there's a secret weapon that I can utilize to work this out quicker!

Michael Udinski

Hey @Bryan your right in thinking that subtracting the two timestamps can get you on your way to calculating the days between payments. The JSON you are providing does have a very interesting format for Date though. If you were using Xano timestamps you could just subtract the two dates, divide by the milliseconds in a day, and maybe use a floor filter to round down to the nearest whole number.

Looks like the data you're bringing in will take some manipulation to get the values isolated before just subtracting the values.

"Date": "\/Date(1518685950940+0000)\/"

Bryan

@Michael Udinski

Since I've posted the topic, I've had a better idea on how to do this, but could still use some guidance.

So I don't need to calculate the dates between each payment but rather the last payment received on the invoice less the invoices due date (which sits outside the payment array, as per the JSON above).

So strategy wise, I'd probably want to get the payments array in a separate variable and sort it in a descending order fashion (by date), and get the 1st element of the array. Is there a way to combine the date manipulation and sort in one function, or would I have to iterate over the whole payments array first, and get valid dates, then only sort that array by date?

Once I have that date though, it should be simple enough for me to do the subtraction