Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Display json with array in a datawindow

  1. How-to
0
Votes
Undo
  1. mathews rutto
    mathews rutto
  2. PowerBuilder
  3. Tuesday, 7 April 2020 12:09 PM UTC

Hello,

I need help when displaying the json response from http://www.mocky.io/v2/5d2810152c000072003ed79e

in a datawindow.

I will really appreciate your help.

Thank you in advance

Cannot install PBv12.0 onto Win10 VM
PB2019 64 bit issues - Internal App Crash?
Accepted Answer
mathews rutto
mathews rutto Accepted Answer Pending Moderation
  1. Sunday, 12 April 2020 13:31 PM UTC
  2. PowerBuilder
  3. # Permalink

Hello,

Here is the complete code that i have used.

HTTPClient http
http = CREATE HTTPClient        
li_rc = http.SendRequest("GET", "http://www.mocky.io/v2/5d2810152c000072003ed79e")
http.GetResponseBody(ls_response)
    
if (li_rc = 1 and http.GetResponseStatusCode() = 200) then //Succeeded
    
    http.GetResponseBody(ls_response)
    
    ll_RowCount = tab_api.tabpage_more.dw_response.ImportJsonByKey(ls_response, ls_Error, Primary!)

     //Checks if any warning
     If len(ls_Error) > 0 Then
        MessageBox("Warning", "With warning information:~r~n" + ls_Error)
     Else
        
        
        JSONParser json
        json = CREATE JSONParser
        
        ls_Json = ls_response
        ls_result = json.LoadString (ls_Json)
        
        ll_root = json.GetRootItem()            
        ll_claims = json.GetItemArray(ll_root, 'claims')            
        ll_cnt_claims = json.GetChildCount(ll_claims)
        
        For i = 1 to ll_cnt_claims
            
            ll_index_claims = json.GetChildItem(ll_claims, i)    
            ll_provider = long(json.GetItemString(ll_index_claims, "payer_prov_code"))
            ls_member_no = json.GetItemString(ll_index_claims, "member_number")
            ll_claim_id = json.GetItemnumber(ll_index_claims, "claim_id")
            lc_amount = json.GetItemnumber(ll_index_claims, "amount")
            lc_gross_amount = json.GetItemnumber(ll_index_claims, "gross_amount")  
            ls_visit_number = json.GetItemString(ll_index_claims, "visit_number")  
            dt_invoice_date = datetime(json.GetItemString(ll_index_claims, "visit_start"))
            ld_invoice_date = date(dt_invoice_date)

            end if
            
            //Invoices.....................................................................
            ll_invoices = json.GetItemArray(ll_index_claims, 'invoices')
            ll_cnt_invoices = json.GetChildCount(ll_invoices)
            
            for ll_inv = 1 to ll_cnt_invoices
                
                setnull(ll_fund)
                setnull(ll_sub)
                
                ll_index_invoices = json.GetChildItem(ll_invoices, ll_inv)
                ll_invoice_id = json.GetItemNumber(ll_index_invoices, "invoice_id")
                ll_bene_id = long(json.GetItemString(ll_index_invoices, "payer_benefit_code"))
                lc_inv_amount = json.GetItemNumber(ll_index_invoices, "amount")
                lc_inv_gross_amount = json.GetItemNumber(ll_index_invoices, "gross_amount")
                ls_invoice_no = json.GetItemString(ll_index_invoices, "invoice_number")
                
                //Invoices line items
                ll_line_items = json.GetItemArray(ll_index_invoices, 'line_items')
                ll_cnt_line_items = json.GetChildCount(ll_line_items)
                
                for ll_items = 1 to ll_cnt_line_items
                    
                    ll_index_line_items = json.GetChildItem(ll_line_items, ll_items)
                    ll_items_id = json.GetItemNumber(ll_index_line_items, "item_id")
                    ls_item_name = json.GetItemString(ll_index_line_items, "prov_item_name")
                    ls_service_group = json.GetItemString(ll_index_line_items, "service_group")
                    ll_quantity = json.GetItemNumber(ll_index_line_items, "quantity")
                    lc_unit_price = json.GetItemNumber(ll_index_line_items, "unit_price")
                    lc_amount = json.GetItemNumber(ll_index_line_items, "amount")
                    dt_charge_date = datetime(json.GetItemString(ll_index_line_items, "charge_date"))
                    
                next
                
            next

            //Diagnosis
            ll_diagnosis = json.GetItemArray(ll_index_claims, 'diagnosis')
            ll_cnt_diagnosis = json.GetChildCount(ll_diagnosis)
            
            for ll_diag = 1 to ll_cnt_diagnosis
                
                ll_index_diagnosis = json.GetChildItem(ll_diagnosis, ll_diag)
                ll_diag_id = json.GetItemNumber(ll_index_diagnosis, "id")
                ls_coding_standard = json.GetItemString(ll_index_diagnosis, "coding_standard")
                ls_diag_name = json.GetItemString(ll_index_diagnosis, "name")
                ls_diag_code = json.GetItemString(ll_index_diagnosis, "code")
                
            next
            
            //Preauth
            ll_preauth = json.GetItemArray(ll_index_claims, 'preauth')
            ll_cnt_preauth = json.GetChildCount(ll_preauth)
            
            for ll_pre_auth = 1 to ll_cnt_preauth
                
                ll_index_preauth = json.GetChildItem(ll_preauth, ll_pre_auth)
                ll_preauth_id = json.GetItemNumber(ll_index_preauth, "id")
                ls_preauth_code = json.GetItemString(ll_index_preauth, "code")
                lc_pre_amount = json.GetItemNumber(ll_index_preauth, "amount")
                ls_authorized_by = json.GetItemString(ll_index_preauth, "authorized_by")
                ls_notes = json.GetItemString(ll_index_preauth, "message")
                ldt_preauth_date = datetime(json.GetItemString(ll_index_preauth, "insert_time"))
                
            next
        
            //Admissions
            ll_admissions = json.GetItemArray(ll_index_claims, 'admissions')
            ll_cnt_admissions = json.GetChildCount(ll_admissions)
            
            for ll_admissions_id = 1 to ll_cnt_admissions
                
                ll_index_admissions = json.GetChildItem(ll_admissions, ll_admissions_id)
                ll_admit_id = json.GetItemNumber(ll_index_admissions, "id")
                ld_admit_date = date(json.GetItemString(ll_index_admissions, "admit_date"))
                ld_discharge_date = date(json.GetItemString(ll_index_admissions, "discharge_date"))
                ls_discharge_summary = json.GetItemString(ll_index_admissions, "discharge_summary")
                ldt_admissions_date = datetime(json.GetItemString(ll_index_admissions, "insert_time")
                
            next
            
        Next
            
        
     End If
    
end if

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Sunday, 12 April 2020 14:09 PM UTC
Thanks for sharing the solution!
  1. Helpful
mathews rutto
  1. mathews rutto
  2. Sunday, 12 April 2020 14:16 PM UTC
Its okay thank you too
  1. Helpful
There are no comments made yet.
Responses (5)
mathews rutto
mathews rutto Accepted Answer Pending Moderation
  1. Friday, 10 April 2020 09:32 AM UTC
  2. PowerBuilder
  3. # 1

Hello Thank you all for your feedback,

I have used JSONParser to extract the JSON data and then populate that into a Datawindow.

I will post my code how if handle it.

Lastly under attachments, how can i handle the image, should i convert to based64bit or how should i handle. That is the only part i am left with.

Comment
mike S
  1. mike S
  2. Friday, 10 April 2020 14:01 PM UTC
Yes, if you have an image or any other blob as part of the json, it is been converted to base64 (string). So you need to convert it back to binary and then save it to a file if you want to display.
  1. Helpful
There are no comments made yet.
Kevin Ridley
Kevin Ridley Accepted Answer Pending Moderation
  1. Thursday, 9 April 2020 14:51 PM UTC
  2. PowerBuilder
  3. # 2

It wouldn't be hard at all to put this into a dw (or probably a better solution would be multiple dws).  I'd probably separate the top level "fixed" data into a dw to look like a header, then put the invoices and items into a treeview dw section by section, then have a separate dw, maybe in it's own tab to display the diagnosis, preauth and admission data.  If there's attachments, you'd obviously have to process those separately.  As mentioned by others, you can use the JSONParser/JSONPackage objects to pull out each section.  Let me know if you need help with a POC.

 

KR

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 8 April 2020 16:55 PM UTC
  2. PowerBuilder
  3. # 3

Hi Mathews;

  I had a look at the JSON being returned and this is considered too "complex" for a DWO to handle. Instead, have a look at the JSONParser object class and it's methods in order to be able to extract the JSON data and then populate that into a DWO with some PowerScript help on your part.

Regards ... Chris

Comment
mathews rutto
  1. mathews rutto
  2. Thursday, 9 April 2020 04:19 AM UTC
Thank you, let me look at JSONParser
  1. Helpful
There are no comments made yet.
Ken Guo @Appeon
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 8 April 2020 08:10 AM UTC
  2. PowerBuilder
  3. # 4

Hi Mathews，

 

The JSON data you provided includes multiple levels of Array data, it doesn’t support being imported to DataWindow directly. Thus you need to read the JSON data one by one and then fill them into the DataWindow via the JsonParse object.

 

You can refer to examples in the links below for how to use the JsonParse object.

https://docs.appeon.com/appeon_online_help/pb2019r2/powerscript_reference/ch10s215.html

https://docs.appeon.com/appeon_online_help/pb2019r2/powerscript_reference/ch10s216.html

 

 

Regards,

Ken

 

Comment
mathews rutto
  1. mathews rutto
  2. Thursday, 9 April 2020 04:20 AM UTC
Thank you,

from the examples given i think it will work out, let me try.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.