Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

Parse JSON for relational database

TimothyB

By TimothyB
in Relationships

 Share

This topic is 3913 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
TimothyB Newbie

TimothyB

Posted
  • Newbies
Posted
Hello there fellow Filemakers!
 
I have recently started to develop a Filemaker solution for the company I work at. I instantly fell in love with Filemaker and I'm looking forward to continue using it.
 
To my problem:
 
We're currently trying to put together a script that can parse a JSON feed with our orders we get from our webshops API and I have managed get all the info I want except one thing. Every order has a order-number and I have managed to get this info and match it with the customer info form the feed.
 
So what I want to do is to export every SKU with the related order-number to another table so that I can use these in a portal for my order and invoice-layout and etc.
 
This is part of the JSON-feed I'm using.
 
{
      "number": 1220,
      "vendorNumber": "B1220",
      "created": "2014-05-05 04:59:42",
      "paymentStatus": "QUEUED",
      "fulfillmentStatus": "NEW",
      "shippingMethod": "DHL Paket/Servicepoint",
      "shippingPerson": {
        "name": "Tdt",
        "companyName": "GB",
        "street": "Ka8",
        "city": "Havdhem",
        "countryCode": "SE",
        "postalCode": "55",
        "stateOrProvinceCode": "I",
        "stateName": "Gots",
        "countryName": "Sweden",
        "phone": "000"
      },
      "paymentMethod": "Faktura - 20 dagar",
      "billingPerson": {
        "name": "Tort",
        "companyName": "Gr",
        "street": "Ka38",
        "city": "Hhem",
        "countryCode": "SE",
        "postalCode": "554",
        "stateOrProvinceCode": "I",
        "stateName": "Gds",
        "countryName": "Sweden",
        "phone": "00"
      },
      "paymentParameters": {},
      "customerId": 13658704,
      "customerName": "Tostedt",
      "customerEmail": "toru",
      "customerIP": "873",
      "customerCountryCodeByIP": "SE",
      "subtotalCost": 13626.0,
      "couponDiscountCost": 0.0,
      "volumeDiscountCost": 0.0,
      "discountCost": 0.0,
      "shippingCost": 120.0,
      "taxCost": 3436.5,
      "totalCost": 17182.5,
      "orderComments": "Dongel",
      "items": [
        {
          "sku": "VZ2501D x 2",
          "name": "Get Current - Vectorworks Fundamentals 2014",
          "quantity": 1,
          "price": 8066.0,
          "weight": 0.0,
          "productId": 35162256,
          "options": [
            {
              "name": "Licenstyp",
              "type": "SELECT",
              "value": "Första Licens"
            },
            {
              "name": "Nuvarande Licens",
              "type": "SELECT",
              "value": "Med Renderworks"
            },
            {
              "name": "Serienummer*",
              "type": "TEXT",
              "value": "32ed9c"
            }
          ],
          "categoryId": 9005208,
          "taxes": [
            {
              "name": "Moms",
              "value": 25.0,
              "total": 2031.5
            }
          ]
        },
        {
          "sku": "VZ2102D x 2",
          "name": "Get Current - Vectorworks Fundamentals 2014",
          "quantity": 1,
          "price": 5560.0,
          "weight": 0.0,
          "productId": 35162256,
          "options": [
            {
              "name": "Licenstyp",
              "type": "SELECT",
              "value": "Följande Licens/er"
            },
            {
              "name": "Nuvarande Licens",
              "type": "SELECT",
              "value": "Utan Renderworks"
            },
            {
              "name": "Serienummer*",
              "type": "TEXT",
              "value": "1421b8"
            }
          ],
          "categoryId": 9005208,
          "taxes": [
            {
              "name": "Moms",
              "value": 25.0,
              "total": 1405.0
            }
          ]
        }
      ],
      "lastChangeDate": "2014-05-05 08:59:42"
    },
    {
      "number": 1219,
      "vendorNumber": "B1219",
      "shippingTrackingCode": "98",
      "created": "2014-04-30 09:00:54",
      "paymentStatus": "CHARGEABLE",
      "fulfillmentStatus": "SHIPPED",
      "shippingMethod": "DHL Paket/Servicepoint",
      "shippingPerson": {
        "name": "Ma",
        "companyName": "Hans",
        "street": "Bar",
        "city": "Ã",
        "countryCode": "SE",
        "postalCode": "55",
        "stateOrProvinceCode": "AB",
        "stateName": "Sto",
        "countryName": "Sweden",
        "phone": "0"
      },
      "paymentMethod": "Faktura - 20 dagar",
      "billingPerson": {
        "name": "M",
        "companyName": "",
        "street": "Ba",
        "city": "a",
        "countryCode": "SE",
        "postalCode": "553",
        "stateOrProvinceCode": "AB",
        "stateName": "St",
        "countryName": "Sweden",
        "phone": ""
      },
      "paymentParameters": {},
      "customerId": 139,
      "customerName": "",
      "customerEmail": "",
      "customerIP": "",
      "customerCountryCodeByIP": "SE",
      "subtotalCost": 13556.0,
      "couponDiscountCost": 0.0,
      "volumeDiscountCost": 0.0,
      "discountCost": 0.0,
      "shippingCost": 120.0,
      "taxCost": 3419.0,
      "totalCost": 17095.0,
      "items": [
        {
          "sku": "VZ2801D x 2",
          "name": "Get Current - Vectorworks Designer 2014",
          "quantity": 1,
          "price": 13556.0,
          "weight": 0.0,
          "productId": 35162255,
          "options": [
            {
              "name": "Licenstyp",
              "type": "SELECT",
              "value": "Första Licens"
            },
            {
              "name": "Nuvarande Licens",
              "type": "SELECT",
              "value": "Med Renderworks"
            },
            {
              "name": "Serienummer*",
              "type": "TEXT",
              "value": "G0875C"
            }
          ],
          "categoryId": 9005208,
          "taxes": [
            {
              "name": "Moms",
              "value": 25.0,
              "total": 3419.0
            }
          ]
        }
      ],
      "lastChangeDate": "2014-05-05 06:43:01"
    },
    {
      "number": 1218,
      "vendorNumber": "B1218",
      "shippingTrackingCode": "",
      "created": "2014-04-30 07:20:31",
      "paymentStatus": "CHARGEABLE",
      "fulfillmentStatus": "SHIPPED",
      "shippingMethod": "DHL Paket/Servicepoint",
      "shippingPerson": {
        "name": "M ",
        "companyName": "",
        "street": "",
        "city": "",
        "countryCode": "SE",
        "postalCode": "",
        "stateOrProvinceCode": "Y",
        "stateName": "",
        "countryName": "Sweden",
        "phone": ""
      },
      "paymentMethod": "Faktura - 20 dagar",
      "billingPerson": {
        "name": "Name",
        "companyName": "",
        "street": "",
        "city": "",
        "countryCode": "SE",
        "postalCode": "",
        "stateOrProvinceCode": "Y",
        "stateName": "V",
        "countryName": "Sweden",
        "phone": "070-268 74 65"
      },
      "paymentParameters": {},
      "customerId": 136158,
      "customerName": "name",
      "customerEmail": "email",
      "customerIP": "",
      "customerCountryCodeByIP": "SE",
      "subtotalCost": 9730.0,
      "couponDiscountCost": 0.0,
      "volumeDiscountCost": 0.0,
      "discountCost": 0.0,
      "shippingCost": 120.0,
      "taxCost": 2462.5,
      "totalCost": 12312.5,
      "orderComments": ".",
      "items": [
        {
          "sku": "VZ2702D x 2",
          "name": "Get Current - Vectorworks Spotlight 2014",
          "quantity": 1,
          "price": 9730.0,
          "weight": 0.0,
          "productId": 35162258,
          "options": [
            {
              "name": "Licenstyp",
              "type": "SELECT",
              "value": "Följande Licens/er"
            },
            {
              "name": "Nuvarande Licens",
              "type": "SELECT",
              "value": "Med Renderworks"
            },
            {
              "name": "Serienummer*",
              "type": "TEXT",
              "value": "6EA1AD"
            }
          ],
          "categoryId": 9005208,
          "taxes": [
            {
              "name": "Moms",
              "value": 25.0,
              "total": 2462.5
            }
          ]
        }
      ],
      "lastChangeDate": "2014-05-05 06:41:29"
    }

I'll attach a PDF (too many characters to fit in the post) with the script I'm using to get the contact-information. I hope it's readable even though some functions have the swedish name.

 

parsepdf.pdf

 

Translation:
Ange variabel = Set variable
Tilldela fält = Assign field
 
 
I'll also attach a photo showing what I'm trying to achieve based on the provided JSON feed in this thread. Notice that the two first SKU's belong to the same order.
 
post-111055-0-55113700-1399447347_thumb.
 
I hope that I have provided you with enough information and I would appreciate all the help I could get!
comment Grand Master

comment

Posted
Posted

a script that can parse a JSON feed with our orders we get from our webshops API

 

Can the API provide the response as XML instead of JSON? If yes, I suggest you take that route, because Filemaker can import XML directly (using a suitable XSLT stylesheet). Parsing JSON manually using text functions is tedious and error-prone.

 

 

I am afraid I didn't understand this part:

 

 

So what I want to do is to export every SKU with the related order-number to another table so that I can use these in a portal for my order and invoice-layout and etc.

 

What does this have to do with your script?

  • Like 1
  • Newbies
TimothyB Newbie

TimothyB

Posted
  • Author
  • Newbies
Posted

Can the API provide the response as XML instead of JSON? If yes, I suggest you take that route, because Filemaker can import XML directly (using a suitable XSLT stylesheet). Parsing JSON manually using text functions is tedious and error-prone.

 

 

I am afraid I didn't understand this part:

 

 

What does this have to do with your script?

 

Thank you for the reply!

 

Unfortunately JSON is my only API response option from the webshop. But maybe there is a way to convert it to a workable XML?

 

 

My current script is extracting the contact information for every order, so it's creating one post for each order with the order number, name, company etc in my contact info table.

 

And what I'm trying to achieve is basically the same thing but I want to extract the products from every order, and here is where I'm facing problems since one order can contain multiple products.

 

So I need to add some lines to my script so that it creates a new post for every product with the sku and order number in a separate table from the contact info table.

 

Just to make things extra clear, if my list looks like this:

Ordernumber1
  Sku1
  Sku2
  Sku3
Ordernumber2
  Sku4
Ordernumber3
  Sku5
  Sku6

This is how I want it to look in the table after extraction:

Ordernumber       Sku
Ordernumber1     Sku1
Ordernumber1     Sku2
Ordernumber1     Sku3
Ordernumber2     Sku4
Ordernumber3     Sku5
Ordernumber3     Sku6

I hope this was clear enough, otherwise just tell me and I'll provide you with the information that is needed.

 

Thanks!

comment Grand Master

comment

Posted
Posted

The way to achieve what you show is to parse out the order number preceding the SKU immediately after parsing the SKU. For example, using your example input, if the $i-th SKU can be parsed out as =

Let ( [
skuPrefix = ""sku":" ;
pos = Position ( $json ; skuPrefix ; 1 ; $i ) ;
start = Position ( $json ; """ ; pos + Length ( skuPrefix ) ; 1 ) ;
end = Position ( $json ; ""," ; start ; 1)
] ;
Middle ( $json ; start + 1 ; end - start - 1 )
)

then the corresponding order number will be =

Let ( [
skuPrefix = ""sku":" ;
orderPrefix = ""number":" ;
skuPos = Position ( $json ; skuPrefix ; 1 ; $i ) ;
pos = Position ( $json ; orderPrefix ; skuPos ; -1 ) ;
start = Position ( $json ; " " ; pos + Length ( orderPrefix ) ; 1 ) ;
end = Position ( $json ; "," ; start ; 1)
] ;
Middle ( $json ; start + 1 ; end - start - 1 )
)

With these two birds in hand, you can now create a new record in the related table, increase $i by 1 and move to the next SKU.

 

That said, parsing JSON this way is very flimsy. I would look into using a plugin or insert a pre-processing stage into the workflow.

  • Like 1
  • Newbies
TimothyB Newbie

TimothyB

Posted
  • Author
  • Newbies
Posted

The way to achieve what you show is to parse out the order number preceding the SKU immediately after parsing the SKU. For example, using your example input, if the $i-th SKU can be parsed out as =

Let ( [
skuPrefix = ""sku":" ;
pos = Position ( $json ; skuPrefix ; 1 ; $i ) ;
start = Position ( $json ; """ ; pos + Length ( skuPrefix ) ; 1 ) ;
end = Position ( $json ; ""," ; start ; 1)
] ;
Middle ( $json ; start + 1 ; end - start - 1 )
)

then the corresponding order number will be =

Let ( [
skuPrefix = ""sku":" ;
orderPrefix = ""number":" ;
skuPos = Position ( $json ; skuPrefix ; 1 ; $i ) ;
pos = Position ( $json ; orderPrefix ; skuPos ; -1 ) ;
start = Position ( $json ; " " ; pos + Length ( orderPrefix ) ; 1 ) ;
end = Position ( $json ; "," ; start ; 1)
] ;
Middle ( $json ; start + 1 ; end - start - 1 )
)

With these two birds in hand, you can now create a new record in the related table, increase $i by 1 and move to the next SKU.

 

That said, parsing JSON this way is very flimsy. I would look into using a plugin or insert a pre-processing stage into the workflow.

 

Wow! Thank you so much for this, it worked like a charm!

 

 

I now get the desired look on my table with order-number   ->     sku

 

 

But I have faced another problem now, I forgot to mention that each product can have a serial number to it.

 

So the final look on the table would be:

 

Ordernumber1     Sku1    Serialnumber1

Ordernumber1     Sku2    Serialnumber2

 

I tried using the scripts you gave me and I almost achieved my goal. My problem is that not all products will have a serial number to it, so when I try to use the script it grabs the correct serial number and puts it the correct order BUT if a product dosen't have a serial number it grabs the next serial number and puts it next to the product who should not have a serial number. It still grabs the correct serial number for the next product even though it was used for the earlier product (who shouldn't have a serial number).

 

So just to sum it up: 

 

I'm using this script to grab the serial number

Let ( [
skuPrefix = "sku:" ;
snPrefix = "Serienummer*" ;
skuPos = Position ( $json ; skuPrefix ; 1 ; $i ) ;
pos = Position ( $json ; snPrefix ; skuPos ; 1 ) ;
start = Position ( $json ; " " ; pos + Length ( snPrefix ) ; 2 ) ;
end = Position ( $json ; "¶" ; start ; 1)
] ;
Middle ( $json ; start + 1 ; end - start - 1 )
)

This almost gives me the desired result except it gives products who shouldn't have serial numbers a duplicate of the next serial number instead of leaving it empty.

 

 

I appreciate all your help!

comment Grand Master

comment

Posted
Posted
if a product dosen't have a serial number it grabs the next serial number and puts it next to the product who should not have a serial number.

 

That's precisely the type of situation your mother warned you about... In fact, I couldn't have come up with a better example for the vagaries of parsing JSON manually, using string functions.

 

If you were addressing the JSON object natively, you would be able to say something like order[0].item[3].serial[0] and get an empty string when the item doesn't have a serial number. Alas, there's no such easy way when you are treating the JSON as text.  You would have to calculate the position of the next item's start, and compare that to the position of the closest serial number in order to decide if the serial number you are considering is within the province of this item. With an exception for the last item in the sequence. Definitely not pretty. I would look for another way.

  • Newbies
TimothyB Newbie

TimothyB

Posted
  • Author
  • Newbies
Posted

That's precisely the type of situation your mother warned you about... In fact, I couldn't have come up with a better example for the vagaries of parsing JSON manually, using string functions.

 

If you were addressing the JSON object natively, you would be able to say something like order[0].item[3].serial[0] and get an empty string when the item doesn't have a serial number. Alas, there's no such easy way when you are treating the JSON as text.  You would have to calculate the position of the next item's start, and compare that to the position of the closest serial number in order to decide if the serial number you are considering is within the province of this item. With an exception for the last item in the sequence. Definitely not pretty. I would look for another way.

 

Yeah, I kind of noticed that for every problem I managed to solve, I discovered several more...

 

So I have started to use the JSON plugin found here: http://www.modularfilemaker.org/2013/08/json/

 

So far, so good. It's much easier to use and so far I've been able to achieve all my current goals with it.

 

Thank you for all your help!

  • Like 1

This topic is 3913 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.

  I accept