LeadSquared Developer Centre

ON THIS PAGE

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.

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.
  • In a single Import action, you can import up to 1-lakh (100,000) rows.
  • The only file format supported while importing is a “.csv” file in the UTF-8 format.
  • You cannot perform the Define Table from CSV action for columns with the User data type.

Mavis DB

 

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.

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”.

Mavis_40

 

Define Table Specifications

On the Table Definition screen, click Define Table Specifications.

LeadSquared Mavis Database

 

Add Custom Fields As 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 custom or system columns to your table.

To add a new custom column 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 allow users to select multiple options on the dropdown, against Allow Multiple Selection, enable the Slider slider.
  4. To display a blank value as the default dropdown value, against Add Blank Row, enable the Slider slider.
  5. Once you’re done, click Save.

LeadSquared - Dropdown datatype in Mavis

 

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
  • LSQ – Lookup
    • User (in your LeadSquared account)

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 If the Mavis feature was enabled on your account after 5th December 2022, text fields have a 10,000-character limit.
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 can create multi-select dropdowns to allow users to select multiple options at once.
  • 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.

If the Mavis feature was enabled on your account after 15th December 2022, JSON fields have a 10,000-character limit.

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 If the Mavis feature was enabled on your account after 5th December 2022, image fields have a 2,000-character limit.
User* green tick icon green tick icon red cross
  • You can add up to 10 subfields under a User data type column.
  • To enable user data type fields in your account, contact your account manager, or write to support@leadsquared.com.

Add System Fields As Columns

Track and manage the creation and modification of records in a Mavis table. You can add the following system fields as columns in your table –

  • Created On (DateTime datatype) – It captures the time stamp when the record was added to the table.
  • Created By (String datatype) – It captures the user who added the record.
  • Modified On (DateTime datatype) – It captures the time stamp when the record was last modified.
  • Modified By (String datatype) – It captures the user who last modified the record.

To add system fields as columns –

  1. Alongside Add Column, click down icon.
  2. Under Add System Fields, alongside the relevant field, click Add.

Note:

    • Only export operations can be performed on system columns.
    • All values in system fields are auto-captured and cannot be manipulated.
    • The CreatedOn & ModifiedOn time stamp will be displayed in UTC format.

 

LeadSquared - Modified on column - Mavis

Configure a User Type Column

*To configure a column containing the User data type –

  1. On the Table Definition screen, click Add Column.
  2. Give a relevant name for the column, and from the Datatype dropdown, select User (for which default configurations will be present). If you choose to modify any of these configurations, click LeadSquared API.
    1. On the User pop-up, under the Select Users & Data References Fields tab, you can modify the following configurations –
      1. Allow Access To – From the following options, select the LeadSquared user whose records you want to store in your table –
        • All Users – All the active users present in your account will be available in the dropdown when adding/updating the User column.
        • Based On Role(s) – The records for the selected user roles will be available in the dropdown when adding/updating the User column. You can select all four user roles if required – Administrator, Marketing User, Sales Manager and Sales User.
        • Based On Team(s) – Select the LeadSquared Teams created in your account. The records of all the users present in the selected team will be available in the dropdown when adding/updating the User column.
      2. Required Data Reference Fields – Select the system (default) user fields you want to display in your table. The User Id and Email Address fields are mandatory, and you cannot remove either. In addition to these two fields, you can add another eight user fields (10 in total).
  3. Once you’re done, click Next.
  4. The User Search & Display Configurations tab is a read-only tab. The Email Address, First Name and Last Name fields are the user fields which can be used to search and identify users from your LeadSquared account. To continue, click Save.
  5. If required, you can make the column Queryable and Nullable. Once you’re done, click Save.
    The user data type column is now added to your table, and the data based on the user type and user fields selected will get populated in the table.

Note:

  • You can only associate the following System User fields to the users present in your Mavis database –
    • First Name
    • Last Name
    • City
    • State
    • Country
    • Phone Number
    • Role
    • Zip Code
    • Team Id
    • Team Name
  • The user field values displayed in the table are static, and the field value in the table will not change if there’s a change in the user field value in your LeadSquared account.
    For e.g., a Row is added with the User column which contains the First Name value as “John”. After a few weeks, if this First Name value for the user is updated to “John Smith” in the LeadSquared account, the User column value will still reflect the First Name as “John” for that record.
  • To enable the User data type for your Mavis account, contact your account manager, or write to support@leadsqured.com.

LeadSquared Mavis Database

 

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 .
Note: You will not be able to edit the Datatype or the Column Id of a column.

LeadSquared Mavis Database

 

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.

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.

LeadSquared Mavis Database

 

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.

On this page

© 2022 by MarketXpander Services Pvt Ltd. All Rights Reserved.