Importing data and setting field values automatically
I'm trying to create an application to track books owed, mailed to others, etc. for our group of BookCrossers.
The app is at http://creator.zoho.com/cheriepie/bookobsessed-owed-books/ and I've still got a few small issues I need help on. First the easy one:
What exactly is the difference between writing a test condition as
if (input.BookCrossingURL != null) -vs-
if (input.BookCrossingURL != "")
I seem to have it only work with using NULL in some instances or only "" in others...
Now on to the much harder stuff. LOL
In a previous app I developed, I was able to use a hidden formula field which combined the two fields BookTitle and BookCrossingURL, into a formula to display the title as a URL as follows:
"<a href='" + BookCrossingURL + "' target='_blank'>" + BookTitle + "</a>"
For that particular app, the BookCrossingURL was a *required* field so that worked out well. Unfortunately, in the app I'm working on now, the BookCrossingURL field is not a required field, so I had to find a way to only display the Title as a URL if the URL is not blank. I searched through this forum and found that I couldn't use if/then/else logic within the Formula field. But then I found this thread http://forums.zoho.com/forumHome.do?forumGroupId=2266000000002001&forumTopicId=2266000000012380&sid=54eb664323f7c5ba887b0199f43d0d39 where a workaround was suggested for something like this. I followed the suggestions there to create a hidden field (TitleLink) with the recommended script actions added to the On Load and On Validate events to populate that field accordingly. (I added to both the Add and Edit areas.) This worked out great for using the form to add data and the views to edit it.
EXCEPT.... In this particular application, the swap moderators will often be submitting their data in a batch, rather than one by one. I figure they'll be doing this via the Import Data action into the form, with the data formatted in a sort of CSV format, but lo and behold, I discovered that the scripts I added don't get triggered when importing data, only when using the actual form to submit.
More forum searching led me to the realization that the (On Add->)On Success event would be able to run the necessary actions to populate the hidden TitleLink field, even when Importing. Here's the script I added to the on Add->on Success event:
//Populate TitleLink field and create hyperlink if BookCrossing URL is entered
if (input.BookCrossingURL != null)
{
input.TitleLink = "<a href='" + input.BookCrossingURL + "' target='_blank'>" + input.BookTitle + "</a>";
}
else
{
input.TitleLink = input.BookTitle;
}
This is one of those cases where I had to use null as opposed to "".
Hurrah! I could now bulk enter data and things work as expected. Sorta... ;) The Book Title is correctly displayed as a hyperlink in the views (actually it's the hidden TitleLink field which is displayed) if a value is included in the BookCrossingURL field, and is displayed regular if not. Unfortunately, now attempts to enter data manually via the form method are screwed up! By screwed up I mean that data entered via the form now always shows up as a hyperlink, even with an BookCrossingURL field. The URL it uses in these cases is the to the application itself (http://creator.zoho.com/cheriepie/bookobsessed-owed-books/). If I remove the above script from On Success, then things go back to working correctly for manual (one-by-one) form entering but now the TitleLink field isn't populated for Imported Data again. *sigh*
Maybe I'm approaching this the wrong way... should I be doing something differently? I was even thinking of rather than using "Import Data", create a 2nd form (if this is even possible) where the swap moderator would enter the Swap Name, Swap Date, and then the equivalent of the CSV data into a text field that I can then have ZC parse and split into the appropriate fields. But that sounds like more trouble than it's worth. Feel free to access my application directly to get a better handle on everything I've discussed here. And HELP ME!!!!!! please.....
And thank you.... :)
The app is at http://creator.zoho.com/cheriepie/bookobsessed-owed-books/ and I've still got a few small issues I need help on. First the easy one:
What exactly is the difference between writing a test condition as
if (input.BookCrossingURL != null) -vs-
if (input.BookCrossingURL != "")
I seem to have it only work with using NULL in some instances or only "" in others...
Now on to the much harder stuff. LOL
In a previous app I developed, I was able to use a hidden formula field which combined the two fields BookTitle and BookCrossingURL, into a formula to display the title as a URL as follows:
"<a href='" + BookCrossingURL + "' target='_blank'>" + BookTitle + "</a>"
For that particular app, the BookCrossingURL was a *required* field so that worked out well. Unfortunately, in the app I'm working on now, the BookCrossingURL field is not a required field, so I had to find a way to only display the Title as a URL if the URL is not blank. I searched through this forum and found that I couldn't use if/then/else logic within the Formula field. But then I found this thread http://forums.zoho.com/forumHome.do?forumGroupId=2266000000002001&forumTopicId=2266000000012380&sid=54eb664323f7c5ba887b0199f43d0d39 where a workaround was suggested for something like this. I followed the suggestions there to create a hidden field (TitleLink) with the recommended script actions added to the On Load and On Validate events to populate that field accordingly. (I added to both the Add and Edit areas.) This worked out great for using the form to add data and the views to edit it.
EXCEPT.... In this particular application, the swap moderators will often be submitting their data in a batch, rather than one by one. I figure they'll be doing this via the Import Data action into the form, with the data formatted in a sort of CSV format, but lo and behold, I discovered that the scripts I added don't get triggered when importing data, only when using the actual form to submit.
More forum searching led me to the realization that the (On Add->)On Success event would be able to run the necessary actions to populate the hidden TitleLink field, even when Importing. Here's the script I added to the on Add->on Success event://Populate TitleLink field and create hyperlink if BookCrossing URL is entered
if (input.BookCrossingURL != null)
{
input.TitleLink = "<a href='" + input.BookCrossingURL + "' target='_blank'>" + input.BookTitle + "</a>";
}
else
{
input.TitleLink = input.BookTitle;
}
This is one of those cases where I had to use null as opposed to "".
Hurrah! I could now bulk enter data and things work as expected. Sorta... ;) The Book Title is correctly displayed as a hyperlink in the views (actually it's the hidden TitleLink field which is displayed) if a value is included in the BookCrossingURL field, and is displayed regular if not. Unfortunately, now attempts to enter data manually via the form method are screwed up! By screwed up I mean that data entered via the form now always shows up as a hyperlink, even with an BookCrossingURL field. The URL it uses in these cases is the to the application itself (http://creator.zoho.com/cheriepie/bookobsessed-owed-books/). If I remove the above script from On Success, then things go back to working correctly for manual (one-by-one) form entering but now the TitleLink field isn't populated for Imported Data again. *sigh*
Maybe I'm approaching this the wrong way... should I be doing something differently? I was even thinking of rather than using "Import Data", create a 2nd form (if this is even possible) where the swap moderator would enter the Swap Name, Swap Date, and then the equivalent of the CSV data into a text field that I can then have ZC parse and split into the appropriate fields. But that sounds like more trouble than it's worth. Feel free to access my application directly to get a better handle on everything I've discussed here. And HELP ME!!!!!! please.....
And thank you.... :)