need help on importing a .csv into two related tables (scripting help?)
I have a ninox.com database with two working tables: Contacts (to track association members) and Events (to track events to which those members are invited). The goal is to import members' attendance after each event, via .csv import. Tables Contacts and Events are correctly linked via a third table called RSVP. I have imported an updated .csv file to a temporary table called StagingImport (a Nioxus video suggested that approach, with a script to update the actual records from here). The first column in the table attached here shows the fields in StagingImports and their field names. The second column shows the fields that need to be updated in the Contacts table and their field names (some are slighly different). The third column shows the fields that need to be updated in Events, and their field names. When I started looking at Ninox's scripting docs I realized I was in over my head. I tried asking ChatGPT (yeah, I know) for a script for a "Process the CSV Import" Button, and needless to say it errors out. The attached doc contains the label info for each table and (on page 2) the script suggested by ChatGPT. I'd appreciate any help you can offer either in correcting the script, or if there's a better (simpler) approach.
62 replies
-
Hi,
You might find the following post interesting.
https://forum.ninox.com/t/x2yk889/is-it-possible-to-use-function-parsecsv-directly-in-server-mode
-
Hi Dave -
You have come to the right place.
First, you can try this version of ChatGPT. It has been taught by our good friend to be better (not perfect) about writing Ninox code. You just have to tell it to respond in English or any language you prefer.
Second, I'm guessing you want the script to create a new Contacts record if the person does not already exist. Or they should already be in the table?
Third, If they do exist then you want to compare contact info and only update info that has changed. That can lead to a bit of mess if you someone mistypes their email address or phone number, you wouldn't want that data to be saved.
Fourth, you will always be creating a new Event by this script.
I would recommend that you create another table called Attendance and make it a child of Event. Then you would add a link to Contacts. That way you have a table that tracks Event info (date, location, etc). Then your Attendance table will track all those who have attended. This table is called a many to many (N:N) table. You can link many Events to many Contacts. Which is what I think you want to do.
Fifth, you would create a new Attendance record for each person that attended linking it to the Event and related Contact record.
Give me some time and I can mock up what this could look like.
-
oops, sorry. Just noticed that you have a linked table called RSVP.
I also just realized that the word doc had the script in it.
So you will have multiple events in the csv?
Are you trying to read a file in the StagingImport table or have you imported the file into separate records and are now processing the whole table?
Can you upload a sample DB?
-
Hi, Fred -
Thanks so much for the response and assistance. Let me try & reply to your questions one at a time.
I tried exporting the database - manual backups are not supported on the paid plan I'm on so that won't work.
WRT new contact records - yes, if the .csv includes either a contact record or an event record that doesn't exist in the DB, i want to create a new one. I can see a situation where a second .csv for the same event might be imported, so while it would usually be the case that the event would always be new, I'd like the ability to re-import the same event if I need to.
As you saw, I do have a dedicated table for the import - I called it StagingImport and it only contains the contact fields and events fields (plus the button I need the script for). FWIW, the .csv contains around 32 fields, most of which I'm not importing. And, yes linking many to many (M:N) is what I need to do. And yes, on import a new record is created in StagingImport for each .csv "row". I'm attaching the .csv file I'm testing with. this is now sitting in my StagingImport table. I've also included a screenshot of the data model, missing the link from StagingImport to Contacts and Events that the button would create (I think?)
(The RSVP table is there to allow manual linking of Contacts to Events for any corrections needed later)
Finally, thx for the GPT link, I'll try that this morning as well.
Thanks for taking your time to dig into this -- much appreciated.
-
I just spent 40 minutes on your ChatGPT link with no luck. We went back & forth a bunch of times (as I had before) and all I could do was keep sending it the error messages I was getting from ninox. Here's a link to that ChatGPT thread, if it helps you at all. (Turns out I did have one error, the zip code in StagingImport was all in caps. I've since fixed that. New doc attached with the latest script your link sent me.
-
It just occurred to me that the error messages I'm seeing might be helpful. I've updated the Word doc with the 5 error messages, right next to the code line where the ninox script editor placed them (sorry they're so small, i wanted them in the right place; you can expand))
-
-
Hi,
You can try the following code inside a button. If it works, we can optimize it further.
do as server let x := select Contacts; let y := select Events; let z := select RSVP; for i in select StagingImport do let c := if contains(x.Email, i.Email) then first(x[Email = i.Email]) else create Contacts.( Email := i.Email; Lastname := i.'Last name'; Firstname := i.'First name'; 'Mobile Phone' := i.'Mobile number'; Zip := i.Zip) end; let e := if contains(y.'Event ID', i.'Event ID') then first(y['Event ID' = i.'Event ID']) else create Events.( 'Event ID' := i.'Event ID'; 'Event Name' := i.'Event name'; 'Event Type' := i.'Event type'; 'Date / Time' := i.'Event Start Date') end; if count(z[Attended = i.Attended and Contacts = number(c.Id) and Events = number(e.Id)]) = 0 then create RSVP.( Contacts := number(c.Id); Events := number(e.Id); Attended := i.Attended); end; end; end-
Progress - THANKS. Three things:
The script is creating multiple records in the events table. That Event ID didn't exist before the import, but the script is creating a new event with the same Event ID for every record in the .csv.
Second - I realize I didn't think "Attended" through properly. This isn't a characteristic of a contact, so I don't think I want an "Attended" field in the contacts table. Instead, it's the connection between the Contact and the Event in each .csv record ... "did this contact attend this event?" It's a True/False in the .csv. So I'm not sure how to record that ... any suggestions? (BTW when I remove rows 25-30 in your script, there are no errors and the script runs fine except for the duplication of events.
Last - when the script is complete, I'd also like it to remove all the records from the StagingImport table if that's possible?
-
Great...
First point: There's a bug in the script. We need to update the "y" variable with the new Event ID so it's recognized in the next iteration.
Second point: You got it. You should create a new field called "Attended" in the RSVP table. It could be a "Yes/No" Ninox field.
Third point: Yes, it's possible.
Try the updated script below and let me know if it works so we can make it even better.
do as server let x := select Contacts; let y := select Events; let z := select RSVP; for i in select StagingImport do let c := if contains(x.Email, i.Email) then first(x[Email = i.Email]) else create Contacts.( Email := i.Email; Lastname := i.'Last name'; Firstname := i.'First name'; 'Mobile Phone' := i.'Mobile number'; Zip := i.Zip) end; let e := if contains(y.'Event ID', i.'Event ID') then first(y['Event ID' = i.'Event ID']) else create Events.( 'Event ID' := i.'Event ID'; 'Event Name' := i.'Event name'; 'Event Type' := i.'Event type'; 'Date / Time' := i.'Event Start Date') end; y := select Events; if count(z[Attended = i.Attended and Contacts = number(c.Id) and Events = number(e.Id)]) = 0 then create RSVP.( Contacts := number(c.Id); Events := number(e.Id); Attended := i.Attended); end; end; delete select StagingImport; end -
New error messages - not finding some fields in the StagingImport table ... ??
-
This button is within each field of the StagingImport table, would that matter here? (I couldn't find a way to create the button at the table level)
-
does the field names of the StagingImport Table corresponds to those in the script?
-
Yes, no change (see screenshot). same names as your previous script which worked
-
the only changes I've made in the previous script were to add two extra lines:
y := select Events;
delete select StagingImport;
Can you use the previous script and gradually add the above lines?
-
can you upload a Demo database and a sample .csv file for testing purposes?
-
My Ninox plan won't let me do a manual backup.
Weird, I looked at each script line by line an only the end changed. I opened the DB in a different browser tab and pasted it in, this time with no errors. Gremlins. i ran the script - the good news is that the staging table emptied on completion but the events are still being created multiple times.
Additional question - I have the RSVP table embedded within the contacts table for the purpose of seeing these results but when I look at contacts, the event is not linked.And when I look at events, the contact is not linked. Am I doing this wrong?
Should I just upgrade my plan and export the entire database for you to look at? I hate taking up so much time. Once we have this figured out I can downgrade again, I guess?
-
Just look at upgrading, once I go to Professional Plan I can't go back to Starter Plan ...
-
I have a second license that isn't active yet. Can i give you those credentials to log into the live account? (there's no real data there yet)
-
Let's try to solve the problem here...
-
you can invite to your workspace.
-
If has also a cloud plan, you could invite him for your team so he can have a look at it. If there are no privacy issues of course....
-
it's asking me for an email address
-
no data yet, so no privacy concern. Once resolved, I can just remove him, correct?
-
True, also try not to copy code from the forum but to overtype it because sometimes there's a problem with that... Just saying...
-
Content aside
- Status Answered
- 17 hrs agoLast active
- 62Replies
- 83Views
-
4
Following