SQL Browser within WCE

Root > 3. Customisation

    Follow WiredContact on Twitter

SQL Browser
The WiredContact SQL Browser will perform the same functions as the standard SQL Browser for your database engine. You can access it from the admin area then Site Settings selcet "Troubleshooting" then select the "SQL" option. In here you will be able to use pure SQL.

You can also edit your HTML templates in the Troubleshooting area and save commands that are run from "buttons" within any layout.

You have the option of exporting your results by clicking on the Save icon after you have run a query. Clicking on the Save icon brings up the export options dialog - you should give your export a name with a .txt or .csv extension so that it opens correctly in the browser. If you want to find the file later, it will be in the "Temp Files" folder under the sites main WiredContact folder.

You can access the SQL Browser's most recently used values by clicking on the forward/reverse arrows above and to the right of the SQL Browser window.

Additional SQL Browser commands to perform WiredContact specific functions:

Parse Names This function will use the existing names (Contact field) in the wce_contact table to fill in the Firstname, Lastname and Salutation fields.
Reset Timestamps This function will reset the timestamp fields specified in Admin...Site Settings...Misc Settings...Type Settings...History Type/Contact edit time stamps. Based on the most recent history entry that matches the appropriate history type, the specified time stamp fields are updated.
Groom [tablename] [fieldname] This function will update the formatting for fields in the the database with an inappropriate format - for example, if you have a phone field that is a text(usage) field, and you want to change it back to a phone(usage) format - you would use:

groom wce_contact phone

and it will return the formatting of the field to match the field usage. This will work for Phone, Integer, Numeric, and Currency field usage types. If the target field is a text field, then it will strip leading and trailing spaces.

adjust [parameter list] This function will adjust specified date/time fields a specific number of minutes (adding or subtracting). For example, you may want to roll foreward certain activities by 1 day (1 day is 1440 minutes). The syntax would be:

adjust table=wce_activity field=starttime value=1440 operator="+" limits="(subject = 'Demo')"

random [parameter list] This function will put a formatted random number in the field specified. You may use a mask parameter to include text data. The mask parameter may contain <#field tags that will be resolved appropriately before the random number is generated.

For example, to randomly generate a password in the ClientPW field in the wce_contact table for contacts that have "Lead" in the ID/Status field, using a format of last name plus 4 random numbers - you would use the following:

random table=wce_contact field=clientpw mask="<#field field=lastname>####" digits=4 limits="idstatus = 'lead'"

which would produce results like:
Huffman0138
Moore6125
Nix8401

alpharandom [parameter list] This function will put a formatted random character string (a..z, 0..9) in the field specified. You may use a mask parameter to include text data. The mask parameter may contain <#field tags that will be resolved appropriately before the random number is generated.

For example, to randomly generate a password in the ClientPW field in the wce_contact table for contacts that have "Lead" in the ID/Status field, using a format of last name plus 4 random numbers - you would use the following:

alpharandom table=wce_contact field=clientpw mask="<#field field=lastname>####" digits=4 limits="idstatus = 'lead'"

which would produce results like:
Huffmanazav
Mooreppvd
Nixusi7

If you need support with this function please email support@wiredcontact.co.uk
Article ID: 493, , Modified: January 5, 2011 at 5:47 PM

Add Feedback

Share this article

FacebookGoogle+TwitterOther Social NetworksPrint