AnsweredAssumed Answered

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

Question asked by whardy7 on Jul 29, 2015
Latest reply on Jul 29, 2015 by 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.

Outcomes