How to fetch a field value from the row above in a “Sub-form”

The Question:

Is there a way to fetch a field value from the row above in a subform and input into the row below?

The Answer:

You need to think about the ways to identify the previous row. One way is to assign a custom row ID (On Add Row) for each row and get value based on that row ID. Another way is to get value based on multiple field value combo (Product Category and Product, for example). Depending on whichever way works best for you, you can write the script.

Below workflow will allow you to assign a custom row ID and get values based on that.

1) Create the Row ID field in the SubForm (Row_ID as field deluge name)

2) In the parent Form (where the SubForm is placed) go to On Add Row of the SubForm field and add the below script

  1. max_row_id = 0;
  2. //Find the previous max row ID
  3. for each subform_row in input.SubForm
  4. {
  5.       if(subform_row.Row_ID > max_row_id)
  6.       {
  7.             max_row_id = subform_row.Row_ID;
  8.       }
  9. }
  10. //Assign the row id for current row
  11. row.Row_ID = max_row_id + 1;

3) In the on user input of SubForm’s field, you can identify the previous row id and then assign the value to the current row. Below script does exactly that.

  1. prev_row_id = row.Row_ID – 1;
  2. //Find the previous max row ID
  3. for each subform_row in input.SubForm
  4. {
  5.       if(subform_row.Row_ID == prev_row_id)
  6.       {
  7.             row.Field_Name = subform_row.Another_Field //add expressions here
  8.       }
  9. }

If a row above is deleted, you will need to handle delete action as well.

Read More

How to receive Twilio Messages in Zoho Creator.

The Problem:

You would like to post the incoming messages to Twilio into Zoho Creator form. Twilio allow specifying a webhook url to post the message data when an incoming sms is received.

Is this possible? You do not want to use any intermediate php or any code?

The Answer:

Yes, it is possible.

Step 1: Create Form fields to receive Twilio Response

Check Twilio’s webhook response parameters and create field names with exact same names. Refer tohttps://www.twilio.com/docs/api/twiml/sms/twilio_request

Step 2: Set up Webhook in Twilio to POST to Zoho Creator

Go to Twilio’s phone numbers page and set up Incoming message action to post to Zoho Creator. Refer to Zoho Creator REST API -> https://www.zoho.com/creator/help/api/rest-api/rest-api-add-records.html

Step 3: Setup Done. Send a Message

And you will see the message in your Zoho Creator Report.

Read More

How to keep XML RPC API Auth Token private in a public form?

The Problem:

You have a public form that we wish to add records into a creator application including is’t subform).

You are able to successfully submit our form data into Creator using the XML RPC API, with the data structure as per the support doc examples –
https://www.zoho.com/creator/help/api/xml-rpc-api/xml-rpc-api-add-records.html#Sample_Request

Now the question is how do you keep the Auth Token private? Anyone who inspects the source code of the form will be able to view the auth token and username.

 

The Answer:

You can create a server file (php or nodejs or any other server page of your choice), to act as a middleware and handle the data post to Zoho Creator. This allows you to store private information on your server so it’s not visible to client. Here’s what I would do in a php file.

file_name: add_record.php

Code:
<?php

define(“authtoken”, “yourAuthToken”);
define(“zc_ownername”, “yourZohoUserName”);
$xml_string = $_REQUEST[“XMLString”];

//Add to Zoho
insert_record($xml_string);

function insert_record($xml_string){
$api_url = “https://creator.zoho.com/api/xml/write”;
$post_params = array();
$post_params[‘authtoken’] = constant(“authtoken”);
$post_params[‘scope’] = ‘creatorapi’;
$post_params [‘XMLString’] = $xml_string;
$post_params[‘zc_ownername’] = constant(“zc_ownername”);

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $api_url);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $post_params);
$result = curl_exec($ch);
curl_close($ch);
}
?>

You can post your form to add_record.php instead of zoho.com url

Read More

Get Japanese Address from post code using Zoho Creator Deluge

The requirement

Input 7 digit Japanese postal code (###-####) and get the address.

Language used

Zoho Creator Deluge

Data Source -> https://github.com/aorborc/jp_code_english/

Deluge Code

Below is the function that takes 7 digits postal code as two inputs (first 3 digits and second 4 digits) and returns an address.

map postal.get_address(string first_three, string second_four)
{
    this_address = map();
    first_three_len = ifnull(input.first_three,"").length();
    second_four_len = ifnull(input.second_four,"").length();
    if ((first_three_len  ==  3)  &&  (second_four_len  ==  4))
    {
        postal_code = input.first_three + input.second_four;
        data_url = "https://raw.githubusercontent.com/aorborc/jp_code_english/0804e74d63ec902eccfcf847ea49a0a6a7488d16/data/" + input.first_three + ".js";
        str = getUrl(data_url);
        value_map = str.toMap();
        my_map = (value_map.get(postal_code)).toMap();
        jp_address = ((my_map.get("perfecture_jp")) + my_map.get("jp_add_1")) + my_map.get("jp_add_2");
        eng_address = ((my_map.get("prefecture")) +" " +  my_map.get("city")) + " " +  my_map.get("town");
        this_address.put("jp_address", jp_address);
        this_address.put("eng_address", eng_address);
    }
    return this_address;
}

Images

Screen Shot 2017-02-07 at 8.38.49 AMScreen Shot 2017-02-11 at 6.07.50 PM

Read More

Zoho Creator URL parameters fix

The Problem

If you are opening a Zoho Creator Form from a third party, and it includes the query string as “?first-name=Rob”, there is no direct way to get the value of “first-name” into a field in your Form. It will not work because Deluge names can’t have “-” in them.

Question posted at : https://forums.zoho.com/topic/dealing-with-a-poorly-formatted-query-string

The Solution

There are only two places in Zoho Creator that can read URL parameters. 1. Form and 2. Pages. Both don’t accept “-” in parameter/field names. So, the optimal solution will be,

  1. Write a php file to convert “-” formatted query strings into “_” formatted query strings and open Zoho Creator Form with new query strings
  2. Host it on your server
  3. Link your hosted php file to your third party

The Php code

I have given the php code below. You can change the Location url to your Zoho Creator Form Url

$query_string = $_SERVER['QUERY_STRING'];
parse_str($query_string, $query_array);
foreach ($query_array as $key => $value) {
$newkey = str_replace("-", "_", $key);
$query_array[$newkey] = $value;
unset($query_array[$key]);
}
header("Location:https://app.zohocreator.com/aorborctechnologies/url-params/#Form:Contact?" . http_build_query($query_array) );

Demo

Click http://aorborc.com/samples/php/demo/param_fix.php?first-name=robert&last-name=m and you will be redirected to our Zoho Creator Form with the formatted query strings.

 

Read More