1 Reply Latest reply on Jul 29, 2015 9:53 PM by whardy7

    Problem populating fields in TableA using records in TableB (complex!)

    whardy7

      Title

      Problem populating fields in TableA using records in TableB (complex!)

      Post

      This might be a challenge to explain, but I'll explain the problem, then explain the application...

      I am trying to populate the fields in Books based on the records in ExtBookData. Each record in ExtBookData has a field whose data includes one FieldTitle from Books with another field containing a calculation to read a JSON element. Every field in Books has a corresponding record in ExtBookData that contains the JSON structure for that particular field. Everything works right up until I try to fill in the field from Books.

      It is a library application that works like this:

             
      1. The user provides an ISBN #
      2.      
      3. I use Google Books API to obtain a JSON (a block of gibberish text containing everything about that book).
      4.      
      5. I create a new Book record and use Base Elements to parse the JSON into the various fields of the Books record. A JSON element looks like this "$.items[0].identifiers.industry.type". There are about 80 different elements. These are all hard-coded and it works great.

      But, I'm looking at a different way for two reason. 1) Google changes the JSON structure often and I would rather change data in one field thnn messing with the hard-coded script, and 2) I'm having to supplement some data with other book lists like OpenLibrary and WorldCat who have different structured JSONs.

      So what I'm trying to do: Instead of populating the fields with hard code of over 240 lines (at least 3 lines per JSON element), I created ExtBookData:: with a field containing each JSON element I need data from (such as "$.items[0].identifiers.industry.type") and a field listing the Books:: field corresponding to that element that the data will go in. I am looping through ExtBookData:: and querying the JSON text file with the elements field and putting it into the corresponding Books field.

      I can provide code if I need to. It is the most challenging problem I've tackled in FM. I know it's possible, but it is stretching my abilities...thank you for any help or pointers.