AnsweredAssumed Answered

Complex import of parsed email fields into a TO where the contents of one imported field has a one-to-many relationship with the target TO

Question asked by garyjones on Jan 19, 2019
Latest reply on Jan 20, 2019 by garyjones



I am using a web service to parse structured emails, containing information about published papers, that then creates a CSV file for import into my Editorial TO data fields - such as title, publication date etc.


Some of the other parsed data sits in reational TOs (where the relationship is Editorial::_pkEditorialID > 'RelatedTO'::_fk'RelatedTO'ID) and my scripting process seems to work (import into EditorialTO first then set the Editorial primary key as a global variable for the import process scripts into other tables).


However, I am struggling with two things;


1). One of the parsed fields is called 'Authors' and the publications will often have more than one author. However, the parsing service cannot split this field to create individual fields for each author. So they are all within one field - example,


"T Niederkrotenthaler, B Till, D Garcia, et al"


These authors will have written more than one publication, so I have normalised their relationship with the Editorial TO by creating a TO instance called ContentAuthor. This relates to the Editorial TO via a join table - ContentAuthor > ContentAuthorRelatedContent > Editorial).


There are also similar TO relationships to cater for the publisher (called 'MediaOutlet') and publication category (pls note the tables related to the Author represent an adoption of Phils value list demo that drives a layout designed for editing purposes.



I cannot work how to split, during the import process, the variable contents of the 'Authors' field into separate data entities that are firstly added to the ContentAuthor table if new, then how to create the requisite records within the ContentAuthorRelatedContent join table that adds the new authors but also any relevant author that may have already beeen in the ContentAuthor TO.


2). My second struggle is around formatting.


a). Within my ContentAuthor TO is a field for initials and my desired formating rule for each is "A.B.", however the parsed data format is "AB" - no full stops. Not clear how to incorporate this with an import script.


b). Ironically, I do need the authors displayed within a single field on a certain key layout and use the following calculation -


Substitute ( List (ContentAuthorRelatedEditorial::ConcatenatedAuthorNameSurnameInitials);"¶";",")


An example output is -


"et al., H.,Morimoto, K.,Uedo, N."


In this instance I can't seem to create a solution that ensures when "et al." is present (which is frequent) it sits at the end of the line where it should.


Thanks for reading this and as always any help much appreciated.