Problems with linked fields while migrating from old application via CSV

95 views
Skip to first unread message

Frank Wittkowski

unread,
Jan 10, 2018, 1:09:36 PM1/10/18
to mementodatabase
Hi all,
migration from my old application does not work, because importing CSV seems not to fill linked fields.
There are some similar post but I don't find a answer to any. So I try, asking again:

Does it work some way or should I surrender?

If so, how can anybody use memento coming from a legacy application with linked data?

I can imagine to import into a normal field and then transfer the content using a Javascript loop into the linked field.
Anybody there who can help with the code?

Thanks a lot
Frank

Frank Wittkowski

unread,
Jan 10, 2018, 3:33:52 PM1/10/18
to mementodatabase
Ok, I have to confess: The problem was the user: Me!
I had a code Problem. My data wasn't UTF-8, so the field name in the CSV file looked correct but wasn't.

I tried also the code and it is rather simple:
It works as an action script.

var libAkt = libByName("B"); // B is the library with detailed Data
var entAkt = libAkt.entries(); // Result is an array with all Datarecords
// var i is looping through records. No Var-Definition or Increment necessary
for (var i in entAkt) {
var str1 = entAkt[i].field( "B1" ); // Put Content from field "B1" of record [i] into variable "str 1"
entAkt[i].set("A", str1); // write content from var "str1" into field "A" (which is our Linked Field to master data library A
};

Bill Crews

unread,
Jan 10, 2018, 4:35:08 PM1/10/18
to Frank Wittkowski, mementodatabase
Interesting! So, you do a set() into a link field to A in B and set it to a string, and it creates a link??

I would've thought you'd need to do a link(). Wow! I have to think about that...

Frank Wittkowski

unread,
Jan 11, 2018, 1:54:19 PM1/11/18
to mementodatabase
For  me it seemed rather normal. I'm an old Oracle and SQL guy. Don't know, how memento works internally, but building a relation (link) with a foreign key (linked field) to the primary key (name fields) of a master dataset (linked table) is not far from any SQL-database. Putting a value into the linked field of the slave dataset to link the 2 records was my first try. Still SQL-like.
Of course: It is only a one-to-many relation. Not sure how many-to-many will work. Or what if the name fields of the master set consist of 2 fields (where one again is a linked field to the master set of the master set = cascading links)?
 
I will try further. For example I want to record a barcode into a barcode field and then use the barcode-number as linked field to cascading sets (stock, article, withdrawels ...)
Thanks for the hint "do a link()". May be I will need this.

The memento documentation is not very detailed. This group and your answers are worth a lot. Hope you get a bonus from LuckyDroid.

Philippe Besnard

unread,
Feb 2, 2018, 2:40:34 AM2/2/18
to mementodatabase
Hi Frank,
In advance, many thanks for the interest you will take in my request !
I have the same problem as you, but not the same scripting knowledge !
I need to import in memento db a primary database ('PATIENTS') and its secondary database ('ACTES' - i.e. every surgeries the patients had - with in each surgery record several datas as pathology, dates, anesthesia type...).
In PATIENT.csv, each patient record has an unique id number called 'id'.
In ACTES.csv, each surgery record has his patient id field, which is the material link between the 2 databases.
In memento db, 'PATIENTS' has a 'Link to Entry' field of type Many-to-Many heading to 'ACTES'.
After importing the csv files, is it possible for a scripting loop to link each 'PATIENTS' record to every matching id in 'ACTES' ?
If impossible, at least, it would be ok if there was only one link to the most recent matching 'ACTES' record (which date field is named... 'date').

Bill Crews

unread,
Feb 2, 2018, 5:26:41 AM2/2/18
to Philippe Besnard, mementodatabase
Here is a script to do what you want. To make it work, I created a field in Acts called Act ID, though it's not necessarily needed; I wanted it during testing, to see that the right things were happening. There really ought to be a Procedures library and maybe Procedure Types and Act Types, as well, but I just made them radio buttons.

Anyway, I matched on Patient ID. Make sure to authorize the script to access the Acts library by pressing the shield icon in the script editor. Put this script into Patients as a library action. If you run it after the library already has data, it'll probably try to add entries again; if you want to do that, you may need some more logic to step over existing acts. But if you have Unique entry names set, it'll just error off, anyway.

var patients = lib().entries();
var actlib = libByName("Philippe Acts");
if (actlib == null) {
   message('Cannot access library "Philippe Acts"');
   exit();
   }
var acts = actlib.entries();

for (var p = 0; p < patients.length; p++) {
   var pat = patients[p];
   var patid = pat.field("ID");
   message("Patient " + patid + ": " + pat.field("Name"));
   for (var a = 0; a < acts.length; a++) {
      var act = acts[a];
      if (act.field("Patient ID") == patid) {
         pat.link("Acts", act);
         message(patid + " -> " + act.field("Act ID"));
         }
      }
   }
message("Done");

Philippe Besnard

unread,
Feb 2, 2018, 6:37:28 AM2/2/18
to mementodatabase
Thank you so much, Bill.
I adapted your script like that and it works like a charm \o/ :

var libPatients = libByName("Patients");
var patients = libPatients.entries();
//
var libActes = libByName("Actes");
if (libActes == null) {
message('Cannot access library "Actes"');
exit();
}
var actes = libActes.entries();


for (var p = 0; p < patients.length; p++) {
var pat = patients[p];

var patid = pat.field("id");
message("Patient " + patid + ": " + pat.field("nom"));
for (var a = 0; a < actes.length; a++) {
var act = actes[a];
if (act.field("id") == patid) {
pat.link("Actes", act);


message(patid + " -> " + act.field("Act ID"));
}
}
}
message("Done");

I think your script will be useful for many memento users who wonder how to import relational databases.
Thanks again !

Bill Crews

unread,
Feb 2, 2018, 9:58:59 AM2/2/18
to Philippe Besnard, mementodatabase
I agree. I'll add some form of it to the wiki.

Philippe Besnard

unread,
Feb 2, 2018, 10:25:51 AM2/2/18
to Bill Crews, mementodatabase
Cool, thanks!

Ph.Besnard
Reply all
Reply to author
Forward
0 new messages