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:
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.
Sometimes a little late-night session brings the answer. I got it figured out. It works really smooth. The code was reduced from 240 lines to 11. I'm thrilled with that!