Problem populating fields in TableA using records in TableB (complex!)
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:
- The user provides an ISBN #
- I use Google Books API to obtain a JSON (a block of gibberish text containing everything about that book).
- 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.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.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.