Manage Tables

Once you’ve created a table, you can set up the table definition in one of the following ways –

  • Define Table From CSV
    Define a table schema by directly uploading a CSV file with your data. Columns in the CSV file are automatically added as Table Column in the Mavis DB. You just need to define the data types and formats and you’re good to go.
  • Select From Available Tables
    Choose from a list of pre-defined tables provided by LeadSquared. Each table is provided with a description of the stored data.
  • Define Table Specifications
    Manually define the table schema, and then add data to the table manually, or through a CSV file with columns that match your defined schema.

Mavis DB

 

Define Table From CSV

This option allows you to upload a CSV file, and define the column schema and add data in one go. It is quicker than the Define Table Specifications option, where you have to first manually define the column schema, and then upload the CSV file to populate the table.

To import an existing CSV file, and to define the table schema based on the file –

  1. Upload your CSV file that contains data into LeadSquared.
    • A sample file is also available for download.
  2. From the Date/Time format dropdowns, specify the Date, Time and DateTime formats in the CSV file (if available), and click Next.
  3. On the Table Definition tab, by default –
    • All the Datatypes are set as a String value, and all the columns are marked Is Nullable.
    • The Mavis Column Name is the same as the CSV Column Name.
    • To change the Column Names, Column Ids, Datatypes and Column properties, please refer to the Define Table Specifications section below.
  4. Once you set the Table Definitions, click Next.
  5. On the Validation tab, if –
    • All the rows are successfully validated, click Next.
    • The validation of some rows have failed, please refer to the Validation section in Table Data screen.
  6. Once the Validation is successful, to begin the Table Import process, click Next.
    • To view the import status, from the Summary screen, click Check Status of Your Request.

Mavis DB

Note: When the Table Import is in progress –

  • All data operations (such as Add Rows, Update Rows, Delete Rows, Truncate Table and Import Data) requests will be queued, until the data is imported.
  • You will not be able to Add, Edit or Delete a column.
  • You will not be able to perform the Restore Table action.

 

Select From Available Tables

To select and import a table into your database –

  1. On the Table Definition screen, click Select From Available Tables.
  2. From the Available Tables screen, click on the relevant table you want to import and click the Use This Dataset button.
  3. On the Import Table pop-up, click Import.

Mavis_40

Notes:

  • To view the data stored within the table, on the Table Definition screen, click Preview LeadSquared table Preview.
  • To know the import status, click the Check Status of Your Request button. This will redirect you to the Request History page, where you can view the status.
  • You cannot add, edit or delete a column within the table, or restore the table, when the Import Table status is “Table Operation Queued” or “Table Operation In-Progress”.

 

Define Table Specifications

On the Table Definition screen, click Define Table Specifications.

LeadSquared Mavis Database

 

Add Columns

Columns can store data across different data types (see table below for a list of supported data types). You can add up to 50 columns to your table.

  1. Click the Add Column button to add a new column.
  2. Once you’re done adding and configuring columns, click Save.

LeadSquared Mavis Database

Note: To increase the maximum number of columns in your table, please get in touch with your account manager, or write to support@leadsquared.com.

If you select Dropdown as the data type –

  1. Against the column marked as Dropdown, click the icon.
  2. On the Dropdown pop-up, against Allowed Option, enter the list of dropdown values. For eg, if the dropdown is for cities, then the values are Bangalore, Mumbai, New Delhi, etc.
  3. To display a blank value as the default dropdown value, against Add Blank Row, enable the Slider slider.
  4. Once you’re done, click Save.

LeadSquared Mavis Database

 

Property Description
Column Name The display name for the column.

  • The minimum character length for a Column Name is 2 characters.
  • The maximum character length for a Column Name 100 characters.
  • The first character of the Column Name must only be an alphabet.
  • The only special character allowed in the Column Name is “_” (underscore).
  • A table cannot contain duplicate Column Names, i.e., two columns with the same name.
  • You can edit the Column Name from the Edit column, even after it is saved.
Column Id The Column Id is the schema name for the column.

While entering the Column Name, this gets auto-populated as the Column Id. If the Column Name contains a space in-between two words, this is replaced by an “_” in the Column Id. For e.g., if the Column Name is “Email Address”, the Column Id will be “Email_Address”.

If required, you can edit the Column Id while creating the column.

  • The minimum character length for a Column Id is 2 characters.
  • The maximum character length for a Column Id 64 characters.
  • If a Column Name exceeds 64 characters, then the Column Id will be auto-populated up to the 64th character that’s present in the Column Name.
  • The only special character allowed in the Column Id is “_” (underscore).
  • A table cannot contain duplicate Column Ids, i.e., two columns with the same Id.
  • You cannot edit the Column Id once it is saved.
Datatypes The list of supported data types are –

  • String
    • String
    • Comma Separated String
    • Text
  • Number
    • Integer
    • Decimal
  • Date & Time
    • Date
    • Time
    • DateTime
  • Others
    • Boolean
    • Dropdown
    • JSON
    • Coordinate
    • Image

To see restrictions for each data type, refer to the table below.

Is Queryable
  • Columns for which you want to query/filter the column data should be marked queryable. To do this, click the Checkbox checkbox under Is Queryable.
  • Only a maximum of 10 columns in a table can be marked as Is Queryable.
Is Nullable
  • To allow null values in your columns, please click the Checkbox checkbox under Is Nullable.
  • Once you allow null values in a column, you cannot mark the same column as Is Unique.
Is Unique To mark a column as unique (a column that contains unique values, such as email address, mobile number, PAN number, Social Security Numbers, etc.), please click the Checkbox checkbox under Is Unique.

 

Datatype Conditions

The conditions for each Datatype are –

Datatype Is Queryable Is Nullable Is Unique Comments
String green tick icon green tick icon green tick icon A string can contain any character, with a maximum character limit of 500.
CommaSeparatedString green tick icon green tick icon red cross
Text red cross red cross red cross
Integer green tick icon green tick icon green tick icon Integer can contain any positive or negative whole number, with a maximum digit limit of 18 (the value is rounded off up to 16 digits, with two zeroes).
Decimal green tick icon green tick icon green tick icon Decimal can contain any positive or negative float value, including 0.
Date green tick icon green tick icon green tick icon
  • Any Date value given in the supported date formats is allowed. For the list of supported date formats, please refer to the table below.
  • The default Date format is “yyyy-MM-dd”.
Time green tick icon green tick icon green tick icon
  • Any Time value given in the supported time formats is allowed. For the list of supported time formats, please refer to the table below.
  • The default Time Format is “HH:mm:ss”.
DateTime green tick icon green tick icon green tick icon
  • Any DateTime value given in the supported DateTime formats is allowed. For the list of supported DateTime formats, please refer to the table below.
  • The default DateTime Format is “yyyy-MM-dd HH:mm:ss”
Boolean green tick icon green tick icon red cross Boolean can only contain true/false values, in case-insensitive order.
Dropdown green tick icon green tick icon red cross
  • The maximum character limit for each dropdown value is 200 characters.
  • You cannot duplicate dropdown values, with the same name.
  • The maximum limit on the number of dropdown values you can add is 200. To increase the values in your dropdown, please reach out to your account manager, or write to support@leadsquared.com.
JSON red cross red cross red cross JSON values should be in valid JSON format.
Coordinate red cross green tick icon red cross
  • Coordinates are passed as Latitude and Longitude.
  • The values for Latitude ranges from -90 to 90.
  • The values for Longitude ranges from -180 to 180.
Image green tick icon green tick icon red cross

 

Date, Time and DateTime Formats

The list of supported Date, Time and DateTime formats are –

Date Time DateTime
dd-MM-yyyy HH:mm:ss dd-MM-yyyy HH:mm:ss
MM-dd-yyyy HH:mm MM-dd-yyyy HH:mm:ss
yyyy-MM-dd yyyy-MM-dd HH:mm:ss
dd-MM-yy dd-MM-yy HH:mm:ss
MM-dd-yy MM-dd-yy HH:mm:ss
dd/MM/yyyy dd/MM/yyyy HH:mm:ss
MM/dd/yyyy MM/dd/yyyy HH:mm:ss
yyyy/MM/dd yyyy/MM/dd HH:mm:ss
dd/MM/yy dd/MM/yy HH:mm:ss
MM/dd/yy MM/dd/yy HH:mm:ss
dd-MM-yyyy HH:mm
MM-dd-yyyy HH:mm
yyyy-MM-dd HH:mm
dd-MM-yy HH:mm
MM-dd-yy HH:mm
dd/MM/yyyy HH:mm
MM/dd/yyyy HH:mm
yyyy/MM/dd HH:mm
dd/MM/yy HH:mm
MM/dd/yy HH:mm

 

Edit Column

To edit an existing column –

  1. Against the column you are looking to edit, under Actions, hover your cursor over Dots.
  2. From the list of options, select Edit, and make the necessary changes to the column.
  3. Once you’re done, click .

LeadSquared Mavis Database

Note: You will not be able to edit the Datatype or the Column Id of a column.

 

Column Name Localization

You can localize the column display name to any of the following languages –

  • English
  • Española Latina (Spanish Latin America)
  • हिंदी (Hindi)
  • Bahasa Indonesia (Indonesian)
  • Português (Portuguese)
  • Tiếng Việt (Vietnamese)
  • Deútsche (German Deutsch)
  • Français (French)
  • Suo̯meŋ Kie̯li (Finnish)

You can add special characters when localizing the column name. The minimum character limit is 1, and the maximum character limit is 100. You can localize the column name when adding a new column, or by editing an existing column.

Note: Only one name can be added per language (locale).

When Adding a Column –

  1. After providing the column name, under Column Name, click LeadSquared API.
  2. From the Select Locale dropdown, choose the language.
  3. Under Column Name, enter the localized name.
  4. To add more options, click Add.
  5. Once you’re done, click Save.

LeadSquared Mavis Database

 

When Editing a Column –

  1. Against the column you are looking to edit, hover your cursor over Dots, and click Edit.
  2. From the Select Locale dropdown, choose the language.
  3. Under Column Name, enter the localized name.
  4. To add more options, click Add.
  5. Once you’re done, click Save, and then click .

LeadSquared Mavis Database

 

Delete Column

To delete an existing column –

  1. Against the column, you’re looking to delete, under Actions, hover your cursor over the Dots icon.
  2. From the list of options, select Delete.
  3. On the Delete Column pop-up, click Confirm.

LeadSquared Mavis Database

Notes:

  • To know the status of the delete request, navigate to the Request History page.
  • When you delete a column, this also deletes all the data stored in this column.
  • Any existing API integration that’s dependent on the deleted column might not work as intended, once the column is deleted.
  • When the delete column Request Status is Table Operation Queued or Table Operation In-Progress –
    • All data operations (such as Add Rows, Update Rows, Delete Rows and Truncate Table) requests will be under Table Operation Queued.
    • You will not be able to Add, Edit or Delete a column.
    • You will not be able to perform the Restore Table action.

 

Switch Tables

To switch between different tables in the same database –

  1. On the Table Definition screen, navigate to the Switch Table dropdown on the top-right corner of your screen.
  2. From the dropdown, select the table you want to switch to.

LeadSquared Mavis Database

 

Next Steps

To know how to perform actions on the Data screen, please refer to Manage Data.