Adding Multiple Records from a Single eMail

I needed to post a number of records into my form, ideally from a single email (and not having to have extra coding on the source system to implement the API). However, Zoho only supports single record submission from a single email. I therefore devised a work-around, which maybe useful to other people needing a similar system. I would also appreciate any comments on code improvement.

My Form (IMPORT) has 10 fields, as below.

Date

Hour

Channel

Site

Break_Type

Month-Year

Guest_Weeks

Promocode

Duration

Total_Billings

To enable multiple record submission, I had the email generation process send the data in a CSV format, using { to mark start of record and } to mark end of record.

The data is then inserted into the body of the email, and submitted to the specified 'forms' address provided by Zoho,

{2013/09/05,14,Site Sales,Lakeside,Music, laughter, shows and dancing,Sep-2013,0.000000000,N/A,7,0.00}
{2013/09/15,12,Site Sales,Sinah Warren,Music, laughter, shows and dancing,Sep-2013,0.000000000,N/A,7,0.00}
{2014/02/17,11,Group,Alvaston Hall,Anchors Away -,Feb-2014,33.714285000,N/A,4,8034.40}
{2014/04/13,8,Group,Nidd Hall,Comedy Time,Apr-2014,26.857142000,N/A,4,7344.48}
{2014/10/01,12,Group,Nidd Hall,Showstoppers,Oct-2014,9.714285000,N/A,4,4130.00}
{2014/11/11,15,Site Sales,Sinah Warren,Top Tribute,Nov-2014,6.000000000,CLUBBROCHURE,3,2632.00}
{2014/11/14,15,Group,Cricket St Thomas,Big Band Sounds,Nov-2014,2.571428000,GROUPLATES,3,954.00}
{2014/11/24,9,Group,Alvaston Hall,Big Band Sounds,Nov-2014,3.428571000,CLUB,3,1684.00}

I used an intermediary form called email_import, as to capture the entire email (using ZC_Content and ZC_Subject).

 I also estimated that I would only receive emails that had less than 100 records in each email, which allowed me to set a loop-routine.

To achieve a loop, I created a simple form called 'Looper' which has only one field, a unique numeric, called 'Counter'. I then populated this form with the values 1 to 100.

I then added code on the 'On Add - On Success' section of my eMail form.

The basis of what the code then does is :

  
   Get the current ID of the email record so it can be deleted when finished
   Count the number of "{" as this indicates the number of records
   Use the count of records and the 'for each record' function to create a pseudo FOR-NEXT loop
   Use basic string functions to find the start & end of each record (put into Rec_Str variable
   Use indexOf to find the commas, then split out the data and store it in a variable
   Once all the variables are collected form that record, write the variables into the target form 
   Remove the first instance (in the data) of { and } - as we have processed that record, and reiterate the loop

It works a treat, and is easier to implement (IMHO) than API...

It would also be a nice enhancement to ZOHO to allow multiple records per email.

Hope this helps someone...

  
   //Get the current record ID so I can delete it when finished
Rec_ID = input.ID;
// Count total number of records in this email
Rec_Counter = getOccurenceCount(input.ZC_Content,"{");
//
for each r in Looper  [Counter < (Rec_Counter  +  1)]
{
    S_Pos = input.ZC_Content.indexOf("{");
    E_Pos = input.ZC_Content.indexOf("}");
    F_Pos = input.ZC_Content.lastIndexOf("}");
    //
    Rec_Str = input.ZC_Content.subString((S_Pos  +  1),(E_Pos  -  1));
    //
    Pos1 = Rec_Str.indexOf(",");
    e_Date = Rec_Str.subString(0,Pos1);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos1  +  1),RE_Pos);
    //
    Pos2 = Rec_Str.indexOf(",");
    e_Hour = Rec_Str.subString(0,Pos2);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos2  +  1),RE_Pos);
    //
    Pos2 = Rec_Str.indexOf(",");
    e_Channel = Rec_Str.subString(0,Pos2);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos2  +  1),RE_Pos);
    //
    Pos2 = Rec_Str.indexOf(",");
    e_Site = Rec_Str.subString(0,Pos2);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos2  +  1),RE_Pos);
    //
    Pos2 = Rec_Str.indexOf(",");
    e_Break_Type = Rec_Str.subString(0,Pos2);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos2  +  1),RE_Pos);
    //
    Pos2 = Rec_Str.indexOf(",");
    e_Month_Year = Rec_Str.subString(0,Pos2);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos2  +  1),RE_Pos);
    //
    Pos2 = Rec_Str.indexOf(",");
    e_Guest_Weeks = Rec_Str.subString(0,Pos2);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos2  +  1),RE_Pos);
    //
    Pos2 = Rec_Str.indexOf(",");
    e_Promocode = Rec_Str.subString(0,Pos2);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos2  +  1),RE_Pos);
    //
    Pos2 = Rec_Str.indexOf(",");
    e_Duration = Rec_Str.subString(0,Pos2);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos2  +  1),RE_Pos);
    //
    Pos2 = Rec_Str.indexOf(",");
    e_Total_Billings = Rec_Str.subString(0,Pos2);
    RE_Pos = Rec_Str.indexOf("}");
    Rec_Str = Rec_Str.subString((Pos2  +  1),RE_Pos);
    //
    insert into Import
    [
        Added_User = zoho.loginuser
        Book_Date = e_Date.toDate()
        Break_Type = e_Break_Type
        Channel = e_Channel
        Duration = e_Duration.toDecimal()
        Guest_Weeks = e_Guest_Weeks.toDecimal()
        Hour = e_Hour.toDecimal()
        Month_Year = e_Month_Year
        Promocode = e_Promocode
        Site = e_Site
        Total_Billings = e_Total_Billings.toDecimal()
    ]
    // 
    input.ZC_Content = input.ZC_Content.removeFirstOccurence("{");
    input.ZC_Content = input.ZC_Content.removeFirstOccurence("}");
}
delete from eMail_Import[ ID == Rec_ID ];