• Help needed

    By Cristian Iovanut 2 decades ago

    I need synchronize Organization.nsf (Contact and Organization) on some fields with a similar table in interbase. I use Delphi and NotesSql. All works fine except that i cannot isert the contact to the apprpriate Organization using nsert statement as Contact.Org_ID is read-only (calculated).

    I strongly need to insert data from oyher source to the desired organization.

    Something like



    SQL = Insert into Contact (FirstName, LastName, JobTitle, Home_phone, Cell_phone, Business_Fax, Email, Salutation, Org_ID)

    VALUES( :v2     , :v1 , :v3 , :v5 , :v6 , :v7 , :v8 , :v10, :v11);



    office@softwiz.ro



    Please help.

    Thank you.

    • Inserting into the contact form.

      By Dave Leigh 2 decades ago

      I'd suggest not using the contact form as the target for your SQL INSERT. Among other things, you're going to need a UniqueID that is unique at high speeds (the default method will generate duplicates if a user creates more than one doc per second). INSERT into a view instead, and provide all the values including setting the form field to "Contact".

      • Insert into a view

        By Cristian Iovanut 2 decades ago

        This would be a great idea. Still I don't know how to create the view and then update the appropriate fields in Contacts.



        As I am new to Lotus Notes I don't know how to create the separate form and how to trigger the update of Ord_ID of Contact from within Lotus Notes, even if I know the Organization UniqueID and I can insert into any table (form).



        I am a little bit puzzled and running out of time.

        Thank you again

        • VIC Contacts and NotesSQL Howto.

          By Dave Leigh 2 decades ago

          It's not letting you update the field because you're specifying an insert into the form… it then tries to respect the field definitions in that form. Instead you're going to need to create a new form to receive your data.



          The procedure for creating the form is no different than you'd use for any other SQL database. You use CREATE TABLE, and the NotesSQL driver comes with all the documentation you need to use it. This was probably installed into the <installeddrive>:\NotesSQL directory.



          For instance, the following test works just fine against VIC…


          Create Table ImportForm<br/>
      (UniqueID CHAR(15),<br/>
      Org_ID CHAR(15),<br/>
      LastName CHAR(15),<br/>
      FirstName CHAR(15))<br/>



          Alter Table ImportForm Add (form char(15))<br/>



          These two statements will create the table (in Notes this will be a form) and then add the necessary "form" field that you'll want to modify after your inputs. (The reason that I alter the table after creation is that I get an error under NotesSQL 3.02 if I try to include that reserved field at the original definition.) Note that none of our fields are calculated. You'll have to do all of the calculations in Delphi, but they're not that hard.



          Then do something like this. Obviously you'd use variable input for the Values, but I'm just testing here (I only put in enough data to know that the calculations are being bypassed and you're able to change the form later.)


          Insert into ImportForm <br/>
(UniqueID, Org_ID, LastName, FirstName) <br/>
Values('AUTO1234','AUTO9876','NotesSQLTest','NotesSQLTest')<br/>



          UniqueID will be whatever unique value you provide from Delphi (you might use a counter, for instance). Again, I tried including a new value for "form" in the INSERT, but that didn't work, so leave it off, and after all your entries have been inserted, issue the following:


          Update ImportForm Set form = 'Contact'<br/>



          This will set all of the forms to 'Contact' so now they'll appear in the VIC views. If you do a couple of SELECTs now you'll see that they no longer appear in the ImportForm table… they've moved to the Contact table.



          This shows in broad terms how accomplish what you want. For this to work to your purposes, you'll have to create the table with all the fields you need for SQL, and you'll have to make sure you've got the right Org_IDs, etc.



          Good luck.