SOLVED Re: $listorrowtojson & $jsontolistorrow ($listtoobjectarray)

Mike Matthews - Omnis omnis at lineal.co.uk
Thu Apr 22 08:55:57 UTC 2021


Well now, I have the solution.  As Bruno was v10.1 and I’m using v10.2, things have moved on a bit.  Giacomo also gave me a hint, and some screen shots from both helped me think a bit.

There are new commands $listtoobjectarray, $listtoarrayarray to play with in v10.2

So now I’m using $listtoobjectarray  to make the JSON in the first place.

Then when I use $jsontolistorrow  to rebuild the original list, I get something useful.

I get a single row, with each column also being a row type.  So I simply step across my temp list #L1, and $merge into my usable list, #L2.

That new list of course need to be defined, so I calculate the finished list definition #L1 as the first column, and then do a $merge.

Do OJSON.$listtoobjectarray(lvList,kUniTypeCharacter,lvErrorText) Returns #S1 ## Convert ROW data into JSON data

Do OJSON.$jsontolistorrow(#S1,lvErrorText,kTrue) Returns #L2 ## Convert ROW data into JSON data

Calculate #L1 as #L2.1.C1 ## Grab the list definition from the first column
For #L2.$line from 2 to #L2.$linecount ## Miss the first line
Do #L1.$merge(#L2.C1)
End For


Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<http://www.lineal.co.uk/>

www.sqlworks.co.uk<http://www.sqlworks.co/>



On 22 Apr 2021, at 09:21, Bruno Del Sol <bruno.delsol at bydesign.fr<mailto:bruno.delsol at bydesign.fr>> wrote:


My test is on 10.1 release 27575 on OSX...


Le 22/04/2021 à 10:18, Mike Matthews - Omnis a écrit :
Just for info, I’m using v10.2 release 30204 on OSX

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.lineal.co.uk%2f&c=E,1,fLv6Ka5tO-1A0qie2GcVZrQ5v66NQmPHXYbRgQNkUyFeDCxva8nF9rv-oFq_6x-MjGIThVw8X7YiOrUKt7wvQn2HvMo4IsJI9NKmzO3m6orMpckj_sIQ47Vx&typo=1>

www.sqlworks.co.uk<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.sqlworks.co%2f&c=E,1,BPSV95m32KIP2eIwLDb1ej1Kqg4gvmLRgQrj2JF_-GtX3aSNRwF4v8-gDFbqUMbMLEQXTIfEYxEDdp3FI4tVsGVV9vfy0FnpNqdf02QT5WQ,&typo=1>



On 22 Apr 2021, at 09:09, Bruno Del Sol <bruno.delsol at bydesign.fr<mailto:bruno.delsol at bydesign.fr>> wrote:


Hi Mike,

I'm afraid you have an error somewhere else because when i do

Do OJSON.$jsontolistorrow(#S1,#S2,kTrue) Returns #L1

and I paste the sample of your data below into #S1, i do get #L1 with 1 column which is a row that has your 12 fields

HTH
Bruno



[{"period":"July-20","1220.00":"25376.450000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"7774.560000","1102.00":"-1383522.860000","1104.00":"0.000000","1105.00":"-307.500000","1385.00":"118375.680000","sum2":"","1386.00":"36712.460000"},{"period":"August-20","1220.00":"29119.270000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"5486.700000","1102.00":"-1230577.190000","1104.00":"0.000000","1105.00":"-294.000000","1385.00":"76638.740000","sum2":"","1386.00":"36712.460000"},{"period":"September-20","1220.00":"31942.070000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"942.100000","1102.00":"-996200.960000","1104.00":"0.000000","1105.00":"-158.500000","1385.00":"76550.000000","sum2":"","1386.00":"36712.460000"},{"period":"October-20","1220.00":"20057.790000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"293.330000","1102.00":"-972919.280000","1104.00":"0.000000","1105.00":"-4706.480000","1385.00":"80589.600000","sum2":"","1386.00":"41377.210000"},{"period":"November-20","1220.00":"32880.320000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"878.670000","1102.00":"-999409.740000","1104.00":"0.000000","1105.00":"-242.910000","1385.00":"80615.200000","sum2":"","1386.00":"39805.210000"},{"period":"December-20","1220.00":"15714.410000","1640.01":"0.000000","sum":"","1640.05":"0.000000","1640.89":"843.080000","1102.00":"-557485.910000","1104.00":"0.000000","1105.00":"-130.000000","1385.00":"77234.130000","sum2":"","1386.00":"39805.210000"},{"period":"January-21","1220.00":"23791.010000","1640.01":"0.000000","sum":"","1640.05":"0.000000","1640.89":"0.000000","1102.00":"-1067034.920000","1104.00":"0.000000","1105.00":"-138.500000","1385.00":"116197.330000","sum2":"","1386.00":"39805.210000"}]

By Design
http://www.bydesign.fr<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.bydesign.fr&c=E,1,3-iSf5LncjUi3_mdsg4ECcZtUB-tsVRETcORYRQZlgCqd_KFnCKlPbXs1RMF3m_x6tbSndtua0yQkr8gVP6Lm_i8GwzkxI8nX8gI9WqVTfY,&typo=1>
Bruno Del Sol
bruno.delsol at bydesign.fr<mailto:bruno.delsol at bydesign.fr>
46, rue de La Tour d'Auvergne
75009 Paris (France)

Le 22/04/2021 à 09:49, Mike Matthews - Omnis a écrit :
Aye, agree, but the result is the same as before.  I don’t get a single list with 12 columns, and 12 rows.

I get a single row, with each column being a list with 1 column and 12 rows.

I could step through and rebuild the original list of course, but what is the point of turning it into JSON if it can’t come back into Omnis land simply?

I shall have a talk with UK Support, and hopefully one of will be put straight :)

Thanks

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.lineal.co.uk%2f&c=E,1,Ew8NKVtbYlYzBU8n5-_B3DSdZH1kWDmykzIroPquX9lYJzShizJkXdEmg3dd8H5K1hv00azQ_kYTVYo49U3QfGfcNEtdwP_UTWj47AClIQBa3A,,&typo=1>

www.sqlworks.co.uk<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.sqlworks.co%2f&c=E,1,sQUVeJ1FWKoXOXLBmwpV-_u-xGFIBHqjUtCBTibmi9-epNQxpAcLTi69TaU93i1RDctPU0TPTx96KE_PmMfqUEqFQHLoNgRdqb6VOKR4bckidmdQqQGI&typo=1>



On 22 Apr 2021, at 08:37, Bruno Del Sol <bruno.delsol at bydesign.fr<mailto:bruno.delsol at bydesign.fr>> wrote:

That's when you should use the bAllowArraysOfRows parameter if I recall my tests

By Design
https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.bydesign.fr&c=E,1,5vgLg2h53Wjef7FteUIiyM8lvdIqmNM93e5sqo-7TPWDveKmpZl2gT-v1HAMzxPTQdBPlIdY0OJJjp4wZSOTImTEyozZ1CukvthI3VqjQW2lIPGWLQ,,&typo=1
Bruno Del Sol
bruno.delsol at bydesign.fr<mailto:bruno.delsol at bydesign.fr>
46, rue de La Tour d'Auvergne
75009 Paris (France)

Le 22/04/2021 à 09:29, Mike Matthews - Omnis a écrit :
Hmm, still not working correctly.

My original list had 12 columns, and 12 rows.

When I try to reconstitute it, each of the original rows comes back as single column list, not a row, with 12 lines.  This is not so easy to add
back when it is not a new row.  I don’t know of a function that will take a list column of x rows and rotate it into a single row with x colums.  Then I could $merge that single row into my list.

It is as if the whole JSON stuff is out by 90 degrees.

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:omnis at lineal.co.uk><mailto:mike.matthews at lineal.co.uk><mailto:mike.matthews at lineal.co.uk>

https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fwww.lineal.co.uk&c=E,1,6j-gQ1lzaooWh0pDiDKAFUMs9_U5OPuT2qlhSIKEtvRwrh_J5OmtBrw1LBMwGgSJIx_lK22_yAJc48KPVkt_gjpZaVo2svVSWbYLVEIaHKCmf9Ta&typo=1<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.lineal.co.uk%2f&c=E,1,NZzr-2Av_5fSh186yA81dEEYnHH3CNhcXILnOmJ34--kd-krO_hE7BqcmHCy4ZuZdQdl932bfSf5FZI_CknGRuKWYOnZKVbKfD55nZKvsSpxLZpBa_r_NQ4Q&typo=1><https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.lineal.co.uk%2f&c=E,1,NZzr-2Av_5fSh186yA81dEEYnHH3CNhcXILnOmJ34--kd-krO_hE7BqcmHCy4ZuZdQdl932bfSf5FZI_CknGRuKWYOnZKVbKfD55nZKvsSpxLZpBa_r_NQ4Q&typo=1>

https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fwww.sqlworks.co.uk&c=E,1,W4U09mNORwy_RgEtTd6j9QCwoiYjUeaT2kJMiFeirnToBGj7BNjQkntT2c58ku30co2XPm0DjIElZ96Ak1YGve5UOh5uIa6UWdsNGbghbSF1pJG_1-Epsv58&typo=1<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.sqlworks.co%2f&c=E,1,WokFhPqGh3u1CPztM6ZVCtr4OrOURtupPsA0urMQskSY5_PSQCaUUXMKg9K3EOiKxa-zst1Fdkn-CZmoV4CrHgGb8QlreQ_So2JOmmK6cJteCUs901N1zsM,&typo=1><https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.sqlworks.co%2f&c=E,1,WokFhPqGh3u1CPztM6ZVCtr4OrOURtupPsA0urMQskSY5_PSQCaUUXMKg9K3EOiKxa-zst1Fdkn-CZmoV4CrHgGb8QlreQ_So2JOmmK6cJteCUs901N1zsM,&typo=1>



On 22 Apr 2021, at 08:10, Giacomo Manzoli <GiacomoM at 888sp.it<mailto:GiacomoM at 888sp.it><mailto:GiacomoM at 888sp.it><mailto:GiacomoM at 888sp.it>> wrote:

Hi Mike, Hi Bruno

In order to obtain the “list of row” instead of a row with a col for each element of the JSON array you have set the third parameter (bAllowArraysOfRows) to kTrue:

Do OJSON.$jsontolistorrow(#S1,lvErrorText,kTrue) Returns #L1 ## Convert
JSON data into List data

After that the method of Bruno should work, I’m using something
similar to perform the same task.

Best regards,

Giacomo Manzoli
Ricerca & Sviluppo
_____________________________________
888 Software Products S.r.l.
Viale Combattenti Alleati d’Europa, 35
45100 Rovigo – Italia
Tel. +39 0425.471240
Fax +39 0425.471239
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fwww.888sp.com&c=E,1,8FeASyjIyEfY6WUvBU-u2Jgr-CqHRC1k-StHqTe01aCrjQcn_vebGPENgkWmTS0jBLyEFeriQ5XRyHujiS_uN3gejm-OSTGx9tqq_Ymc_yK4W_Jbs26eqWs,&typo=1<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.888sp.com%2f&c=E,1,JDRa9jDnzg4l78NxZal5Ucf5TS1hbNv4R-xHjeRDGOOrNnxC9MhKC-HCg_YZ43gQq_5Jy3OH18nlk46BHmg4EoPapww48k_Qq35P7wUdMEeym6W38_hhJaK0qwQ,&typo=1><https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.888sp.com%2f&c=E,1,JDRa9jDnzg4l78NxZal5Ucf5TS1hbNv4R-xHjeRDGOOrNnxC9MhKC-HCg_YZ43gQq_5Jy3OH18nlk46BHmg4EoPapww48k_Qq35P7wUdMEeym6W38_hhJaK0qwQ,&typo=1>

Da: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com><mailto:omnisdev-en-bounces at lists.omnis-dev.com> per conto di Bruno Del Sol <bruno.delsol at bydesign.fr><mailto:bruno.delsol at bydesign.fr>
Data: giovedì, 22 aprile 2021 08:57
A: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com><mailto:omnisdev-en at lists.omnis-dev.com>, Mike Matthews - Omnis <omnis at lineal.co.uk><mailto:omnis at lineal.co.uk>
Oggetto: Re: $listorrowtojson & $jsontolistorrow
Hi Mike,

I ran into this issue recently, and since I didn't want to use a full OJSON object to deal with it i came up with a
quick and dirty workaround. The method below takes the list of rows returned by $jsontolistorrow (ie #L1 in your
example) or by the pContents var in POST calls and fixes it.

(Thanks to you if you post an ER to your East coast comrades asking if they could have $jsontolistorrow to do that directly)


$listofrows2list
--------------------

# parameter json is a list (ie was a js array) with a single col which is
a row (ie was a js object)
# returns a list

If not(json.$colcount=1)
Quit method #NULL ## not a list of rows
End If

Calculate row as json.1.C1

If row.$colcount=0
Quit method #NULL ## not a row
End If
Do returnlist.$copydefinition(row)

Calculate json.$line as 0
While json.$next(0)
Do returnlist.$add()
Calculate returnlist.$line as returnlist.$linecount
Do returnlist.$assignrow(json.c1,1)
End While

Quit method returnlist


Regards
Bruno

By Design
https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.bydesign.fr&c=E,1,tyMcQAy0e38iF2DZUqTmhDpcrjFT8Pq_iu3bbQ4zN6EufAobG8Z2LyOrE9L2y4h7N6Qezmc9k19r9RtjvvH7magPWC3vWLm5jrIs8BxbePL_1GMCVA,,&typo=1
Bruno Del Sol
bruno.delsol at bydesign.fr<mailto:bruno.delsol at bydesign.fr>
46, rue de La Tour d'Auvergne
75009 Paris (France)

Le 21/04/2021 à 20:58, Mike Matthews - Omnis a écrit :
Hello All,

I know I can do this, but I just can’t remember, so a Senior Moment for me.

Do OJSON.$listorrowtojson(lvList,kUniTypeCharacter,lvErrorText) Returns
pfList.vat_bas_nom_json ## Convert LIST data into JSON data

Calculate #S1 as pfList.vat_bas_nom_json ## Check JSON data

Do OJSON.$jsontolistorrow(#S1,lvErrorText) Returns #L1 ## Convert JSON data into List data

So the problem here is #L1 does not look like lvList, by a long way, just a single row, each cell with a list.  I was hoping the JSON was self contained, but no.

lvList has 12 columns, with 12 rows.  All Chars columns.

The JSON data looks like this:

[{"period":"July-20","1220.00":"25376.450000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"7774.560000","1102.00":"-1383522.860000","1104.00":"0.000000","1105.00":"-307.500000","1385.00":"118375.680000","sum2":"","1386.00":"36712.460000"},{"period":"August-20","1220.00":"29119.270000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"5486.700000","1102.00":"-1230577.190000","1104.00":"0.000000","1105.00":"-294.000000","1385.00":"76638.740000","sum2":"","1386.00":"36712.460000"},{"period":"September-20","1220.00":"31942.070000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"942.100000","1102.00":"-996200.960000","1104.00":"0.000000","1105.00":"-158.500000","1385.00":"76550.000000","sum2":"","1386.00":"36712.460000"},{"period":"October-20","1220.00":"20057.790000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"293.330000","1102.00":"-972919.280000","1104.00":"0.000000","1105.00":"-4706.480000","1385.00":"80589.600000","sum2":"","1386.00":"41377.210000"},{"period":"November-20","1220.00":"32880.320000","1640.01":"1.110000","sum":"","1640.05":"0.000000","1640.89":"878.670000","1102.00":"-999409.740000","1104.00":"0.000000","1105.00":"-242.910000","1385.00":"80615.200000","sum2":"","1386.00":"39805.210000"},{"period":"December-20","1220.00":"15714.410000","1640.01":"0.000000","sum":"","1640.05":"0.000000","1640.89":"843.080000","1102.00":"-557485.910000","1104.00":"0.000000","1105.00":"-130.000000","1385.00":"77234.130000","sum2":"","1386.00":"39805.210000"},{"period":"January-21","1220.00":"23791.010000","1640.01":"0.000000","sum":"","1640.05":"0.000000","1640.89":"0.000000","1102.00":"-1067034.920000","1104.00":"0.000000","1105.00":"-138.500000","1385.00":"116197.330000","sum2":"","1386.00":"39805.210000"},{"period":"February-21","1220.00":"25510.250000","1640.01":"0.000000","sum":"","1640.05":"0.000000","1640.89":"0.000000","1102.00":"-689436.870000","1104.00":"0.000000","1105.00":"-802.730000","1385.00":"7711

So how do you correctly turn a List into JSON, and then back again?

Thank you

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:omnis at lineal.co.uk><mailto:mike.matthews at lineal.co.uk><mailto:mike.matthews at lineal.co.uk>

https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fwww.lineal.co.uk&c=E,1,IKVyoW8GN8kuDgOakoV6_jV6F645G_H0a8yYrK9SzGARKLE0cXsSjcR1xxL7KCcOovgg0MINAj5C4LpF0b0oTCr5R74g07H9zACDOSNfSbk,&typo=1<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.lineal.co.uk%2f&c=E,1,6OYtNAl-5WxLML3uWcrxhxZUzpxkga9izgLCYrxIBrDz4HMpXb3z-20iUDgIWJolW4xTcB0e_-JyMcQb0oMH6nv1Dlz-hlKtgEIyn1qzZ3W1Q14_alhyXQ,,&typo=1><https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.lineal.co.uk%2f&c=E,1,6OYtNAl-5WxLML3uWcrxhxZUzpxkga9izgLCYrxIBrDz4HMpXb3z-20iUDgIWJolW4xTcB0e_-JyMcQb0oMH6nv1Dlz-hlKtgEIyn1qzZ3W1Q14_alhyXQ,,&typo=1>

https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fwww.sqlworks.co.uk&c=E,1,32TyI3aF7yzl7TLbPsCjvBgBzGwypvm5iJbMFRdiF77Uergmuj99wl3hz1kyzvfA_g_3lkjROJHpJgmYpn73p882w24fh-65B4cSL-kKrWHkGDRIKYCcyRQ,&typo=1<https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.sqlworks.co%2f&c=E,1,1rQhNqniQwBepL1G0b__dGe2KwQ3aUjeKHGsGFuxJteyzgGFFVnYpcMtWDBDQWqzzc_kSkr-FvjFfxxHnXrDR1wTRgZdZOkn375qjYQ-3o5laW-NPSwZMg,,&typo=1><https://linkprotect.cudasvc.com/url?a=http%3a%2f%2fwww.sqlworks.co%2f&c=E,1,1rQhNqniQwBepL1G0b__dGe2KwQ3aUjeKHGsGFuxJteyzgGFFVnYpcMtWDBDQWqzzc_kSkr-FvjFfxxHnXrDR1wTRgZdZOkn375qjYQ-3o5laW-NPSwZMg,,&typo=1>



_____________________________________________________________
Manage your list subscriptions at https://linkprotect.cudasvc.com/url?a=http%3a%2f%2flists.omnis-dev.com&c=E,1,t3Cv88-4ayGjWO4nz8-EdtuVdS0X36kp_zBFGE9i0uqZJKziOINp_bbcazDM4Mz4bcVRBfE9q1EXJMbLhMOg6rYGrtISiJjLaf85g4ha7zAx7lOHJwNW-jqYCw,,&typo=1
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at https://linkprotect.cudasvc.com/url?a=http%3a%2f%2flists.omnis-dev.com&c=E,1,cb3QUJTWn8EyO_PkOnqMHvp_J3RyrBPDjlAm6V--pux_Bex0AWYzFn8uqYKFkbifEIMVRm4svcz7lK1BDhUu1ozN17YNprLbfeQTX1eE&typo=1
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
_____________________________________________________________
Manage your list subscriptions at https://linkprotect.cudasvc.com/url?a=http%3a%2f%2flists.omnis-dev.com&c=E,1,cRavwsCLahLJlcjtq-dhLE5fBDH1cqnxG3H8uHQoMtziDyOmETkKgEK_-vitw6AzfnWca0OseCTBha7CKKeKtXbnqI844WQYphCjlS5K&typo=1
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at https://linkprotect.cudasvc.com/url?a=http%3a%2f%2flists.omnis-dev.com&c=E,1,TA9rQuQd-0jXUFOK9jBU21cS-Qax7DeY-6jEkCinflNKRXD2VrIYiyJY0TrRq_m6QhVUZEdotTtK0Az1QhewcEE181Stv1Oiu_0RPaNmrc48sXMT_4_zym1x&typo=1
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com






More information about the omnisdev-en mailing list