Query to Json (1 Viewer)

indrajeetpramalik

New member
Local time
Today, 17:17
Joined
Aug 14, 2019
Messages
15
Hi, I have attached a database that can generate Json from a Query data.
But I am trying to get Json output from a query in a specific format. (Expected Json format attached)

My Database contains 3 tables
1.Bill - Contains Invoice no. and customer details
2.BillProducts - Contains products related to Invoices
3.BillPayments - Contains Payment type breakdown related to Invoices

ONE TO MANY RELATIONSHIPS done with 'VouNo'

Using JsonConverter Module from Github.
 

Attachments

  • Demo.zip
    92.1 KB · Views: 315

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,358
Hi. I've only used the JsonConverter to parse JSON strings into VBA. I didn't know it can generate JSON from a query. Are you sure? I'll go check it again...
 

indrajeetpramalik

New member
Local time
Today, 17:17
Joined
Aug 14, 2019
Messages
15
Hi. I've only used the JsonConverter to parse JSON strings into VBA. I didn't know it can generate JSON from a query. Are you sure? I'll go check it again...
Yes the attached database can convert query to json.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,358
Yes the attached database can convert query to json.
Sorry, I didn't look at your attachment. I thought you meant it only contained the JSON format you wanted to create. Let me take a look...
 

indrajeetpramalik

New member
Local time
Today, 17:17
Joined
Aug 14, 2019
Messages
15
Sorry, I didn't look at your attachment. I thought you meant it only contained the JSON format you wanted to create. Let me take a look...
Yes please have a look. Attachment contains a .mdb file and a .txt file as json example.
 

isladogs

MVP / VIP
Local time
Today, 11:47
Joined
Jan 14, 2017
Messages
18,186
Hi
First of all, thanks for alerting me to the fact that Tim Hall's code could also be used to create JSON.
However, as written, the code cannot handle subarrays such as those in your ItemList subarray

You can easily add the remaining fields you require by adding tblProducts to your query.
However, doing that will create a single array in your JSON which isn't what you want

1625512641849.png


Similarly, adding tblPayments will then include the two fields in your PaymentMode subarray but once again the output will be a 'flat' structure

Remember that Access cannot handle JSON files automatically.

In order to get your desired result, you will probably need to do one of the following:
1. Rewrite the code to handle the subarray. I suspect that could be a lot of work
2. Save the data as a CSV file and use a free online CSV to JSON converter
3. Save as CSV and export to Excel Get & Transform
4. Purchase a commercial app to handle the conversion
The best solution may depend on whether this is a one-off task or will be a regular event.

For info, my own JSON app can import JSON data into Access tables ... but not export data as JSON

EDIT: For info, the expected JSON file you supplied isn't a valid JSON file. For example:
a) the number field values should not have surrounding quotes
b) there shouldn't be a comma after the Amount Payable values as its the final field
c) I believe the PaymentModes subarray needs to be part of each record ...NOT listed separately
 
Last edited:

indrajeetpramalik

New member
Local time
Today, 17:17
Joined
Aug 14, 2019
Messages
15
Hi
First of all, thanks for alerting me to the fact that Tim Hall's code could also be used to create JSON.
However, as written, the code cannot handle subarrays such as those in your ItemList subarray

You can easily add the remaining fields you require by adding tblProducts to your query.
However, doing that will create a single array in your JSON which isn't what you want

View attachment 92821

Similarly, adding tblPayments will then include the two fields in your PaymentMode subarray but once again the output will be a 'flat' structure

Remember that Access cannot handle JSON files automatically.

In order to get your desired result, you will probably need to do one of the following:
1. Rewrite the code to handle the subarray. I suspect that could be a lot of work
2. Save the data as a CSV file and use a free online CSV to JSON converter
3. Save as CSV and export to Excel Get & Transform
4. Purchase a commercial app to handle the conversion
The best solution may depend on whether this is a one-off task or will be a regular event.

For info, my own JSON app can import JSON data into Access tables ... but not export data as JSON

EDIT: For info, the expected JSON file you supplied isn't a valid JSON file. For example:
a) the number field values should not have surrounding quotes
b) there shouldn't be a comma after the Amount Payable values as its the final field
c) I believe the PaymentModes subarray needs to be part of each record ...NOT listed separately
Thanks for reply. Yes there are few mistakes in the expected json file as i typed it manually. So can you alter the query structure through which i can get the desired CSV file? This is going to be regular event, infact this process will be used to generate e-invoice from goverment prortal. We need to POST Invoice details in JSON and GET a uniqueID which goverment returns and put that on invoice issued. This will be used every time making a invoice.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 11:47
Joined
Jan 14, 2017
Messages
18,186
The following query will give you a flat file CSV structure:
Code:
SELECT Bill.VouNo, Bill.CustomerName, Bill.Address, Bill.Phone, BillProducts.SerialNo, BillProducts.Product, BillProducts.Qty, BillProducts.Rate, BillProducts.AmountPayable, BillPayments.PaymentMode, BillPayments.AmountPaid
FROM (Bill INNER JOIN BillProducts ON Bill.VouNo = BillProducts.VouNo) INNER JOIN BillPayments ON Bill.VouNo = BillPayments.VouNo
WHERE (((Bill.VouNo)="INV-1"));

However that won't allow you to generate the JSON file you want direct from Access. Doing that will likely be a lot of work
Or, as already mentioned, use an external tool to do this
 

indrajeetpramalik

New member
Local time
Today, 17:17
Joined
Aug 14, 2019
Messages
15
The following query will give you a flat file CSV structure:
Code:
SELECT Bill.VouNo, Bill.CustomerName, Bill.Address, Bill.Phone, BillProducts.SerialNo, BillProducts.Product, BillProducts.Qty, BillProducts.Rate, BillProducts.AmountPayable, BillPayments.PaymentMode, BillPayments.AmountPaid
FROM (Bill INNER JOIN BillProducts ON Bill.VouNo = BillProducts.VouNo) INNER JOIN BillPayments ON Bill.VouNo = BillPayments.VouNo
WHERE (((Bill.VouNo)="INV-1"));

However that won't allow you to generate the JSON file you want direct from Access. Doing that will likely be a lot of work
Or, as already mentioned, use an external tool to do this
I already tried with this flat query but didnt get desired result. I exported this flat query data to Excel and covnverted to JSON with online converter. This is what i got..

[
{"VouNo":"INV-1","CustomerName":"FRANKY","Address":"USA","Phone":"13432524211","SerialNo":"1","Product":"BREAD","Qty":"2","Rate":"10","AmountPayable":"20","PaymentMode":"CASH","AmountPaid":"9"},
{"VouNo":"INV-1","CustomerName":"FRANKY","Address":"USA","Phone":"13432524211","SerialNo":"2","Product":"BUTTER","Qty":"3","Rate":"5","AmountPayable":"15","PaymentMode":"CASH","AmountPaid":"9"},
{"VouNo":"INV-1","CustomerName":"FRANKY","Address":"USA","Phone":"13432524211","SerialNo":"3","Product":"JAM","Qty":"1","Rate":"4","AmountPayable":"4","PaymentMode":"CASH","AmountPaid":"9"},
{"VouNo":"INV-1","CustomerName":"FRANKY","Address":"USA","Phone":"13432524211","SerialNo":"1","Product":"BREAD","Qty":"2","Rate":"10","AmountPayable":"20","PaymentMode":"CARD","AmountPaid":"30"},
{"VouNo":"INV-1","CustomerName":"FRANKY","Address":"USA","Phone":"13432524211","SerialNo":"2","Product":"BUTTER","Qty":"3","Rate":"5","AmountPayable":"15","PaymentMode":"CARD","AmountPaid":"30"},
{"VouNo":"INV-1","CustomerName":"FRANKY","Address":"USA","Phone":"13432524211","SerialNo":"3","Product":"JAM","Qty":"1","Rate":"4","AmountPayable":"4","PaymentMode":"CARD","AmountPaid":"30"}
]

.Ofcourse this is not what i want. Please help me with a best possible solution. Atleast suggest or alter the mdb file so that i can get a excel output that can be converted to desired JSON.
 

isladogs

MVP / VIP
Local time
Today, 11:47
Joined
Jan 14, 2017
Messages
18,186
That's exactly what I expected to happen.
I'm sorry but I've already given you the possible solutions I can think of.
Perhaps I'm wrong but to do this in Access will almost certainly be a lot of work (many hours!).
it really not something I have time to do at the moment even as a chargeable task.

At some point in the future, I may add code to my JSON app to allow it to export JSON files but that's not possible at the moment

Suggest you look into using Excel Get & Transform or PowerShell or even Power Automate Desktop
 

indrajeetpramalik

New member
Local time
Today, 17:17
Joined
Aug 14, 2019
Messages
15
That's exactly what I expected to happen.
I'm sorry but I've already given you the possible solutions I can think of.
Perhaps I'm wrong but to do this in Access will almost certainly be a lot of work (many hours!).
it really not something I have time to do at the moment even as a chargeable task.

At some point in the future, I may add code to my JSON app to allow it to export JSON files but that's not possible at the moment

Suggest you look into using Excel Get & Transform or PowerShell or even Power Automate Desktop
Umm.. that's disappointing. Well i must try alternative way and hope to get a solution to this in future. Thanks bro.
 

isladogs

MVP / VIP
Local time
Today, 11:47
Joined
Jan 14, 2017
Messages
18,186
Tim Hall's code is a great start into integrating JSON with Access but it does have limitations.
If you do find a wholly Access-based solution, I would be glad to hear of it.

But in the meantime I suggest you look into all the alternatives I suggested
 

indrajeetpramalik

New member
Local time
Today, 17:17
Joined
Aug 14, 2019
Messages
15
Tim Hall's code is a great start into integrating JSON with Access but it does have limitations.
If you do find a wholly Access-based solution, I would be glad to hear of it.

But in the meantime I suggest you look into all the alternatives I suggested
Finally done it bro. Cheers!!
ScrnSht.jpg
 

isladogs

MVP / VIP
Local time
Today, 11:47
Joined
Jan 14, 2017
Messages
18,186
Congratulations. Now please can you upload your solution to assist others

However, I'm still not sure that the placement of the PaymentDetails subarray is correct
 

Users who are viewing this thread

Top Bottom