  40 | Auditing | Auditing keeps track of who created an entry into the table and when. Adding auditing to your tables will allow you to use the [date search] feature to find all items created on a particular date.
You can also use auditing to quickly find all the items created by a particular user or setup security so that users can only see the items they have created.
<img src="/attachments/71aa356f-a1e3-4e2f-a880-3d8095cc6f6b/Auditing.png">
<strong>Please note the warning. To have the functionality to sync with your laptop you must select Auditing and Versioning.</strong> | Data Analysis |
| 41 | Picklists | See [Look-up Tables]. | Definitions |
| 42 | Datatype | A datatype is a type of data. Some data types include currency, decimals, text, date/time, yes/no and [look-up tables]. Choosing a datatype allows you increase data accuracy by restricting which kind of information users can enter when they are creating new records.
<h2>Data Accuracy</h2>
Choose a datatype that accurately describes which type of data a field will contain will make your application more efficient and less error-prone. It will also make any reports and charts you generate more precise. Think carefully about which datatype is most appropriate for your field. If you create a field called HireDate in your Employees table, choose the datatype date/time, users will only be able to enter properly formatted dates. If you chose a less specific datatype, such as Multi-line text, employees could enter the entire story of how they were hired in the HireDate field. | Definitions |
| 46 | Grid View | The "grid view" or "list view" is a view of a [database table] formatted as a table. By default, a grid view shows you all of the rows and columns in a table. It is represented with this icon <img align="absmiddle" alt="List View" src="/icons/crystal_project/32x32/actions/view_text.png">.
You can usually get to the grid view by going to the menu tab and clicking the "grid view" sub-menu tab.
<img width="600" class="border" src="/attachments/developer.qrimp.com-f85a60de-50b6-40c5-9930-5a1cefe62109/defaultview.png">
By default, most views of your data have a link to the grid view in the [Content Header] above your data.
Click the id link or double click a row to view the record. Click the edit icon to edit.
<h2>Deleting and editing rows in grid view:</h2>
Next to the id field in most tables you will see two icons, a delete icon <img src="images/delete.gif" alt="delete icon"/> and an edit icon <img src="/images/edit.gif" alt="edit icon"/>.
Clicking the delete icon allows you to delete the row from the data table (it warns you if you want to delete first).
Clicking the edit icon allows you to change data in that row.
<img src="/attachments/4ec6c6cc-f65f-4668-bdc6-5837864d83d1/GridView1.png">
To add additional icons to your id columns, use [Field Templates].
| Views |
| 47 | Add a Record | You can add records to a [database table] from anywhere you see the "Add New" icon (a piece of paper with a green plus sign).
<h2>Adding a New Record</h2>
Navigate to the [grid view] of the table you want to update and add a row to that table by clicking the add new icon <img align="absmiddle" src="/attachments/117ed1d1-e1de-4622-a31b-351e439d2946/AddNewButton.png"> in the [Content Header]. All records added to your tables are given [AutomaticIDs].
<img src="/attachments/b1e60dd6-4fa8-40e1-a7f5-25ee77c632e1/AddNewColumn.png" /> | Working with Data |
| 48 | Square Brackets | An Introduction to Qrimp</textarea>
and produces this:
You can show a "friendly name" for items in the detail view, rather than the id number of the field. (See also [AutomaticIDs]
For example if you have an address table with a foreign key to the states table, when displaying the address, you'd like the state name to be displayed instead of the unique id for the state. To do this, you'd use something like the following:
[states:48] where 48 is the ID of the element to be displayed from the states table.
If this item is being displayed in the grid view, you can dynamically insert the Foreign key ID value into the square brackets like this:
<noeval>[states:[DATA]]</noeval> uses the data in the database for this cell to grab the state name. By default, Qrimp will display this notation in the web page with a link back to the detail page for the table and item specified by [DATA]. To show only the data without the link back to the item, use [states:[DATA]:nolink].
<noeval>[</noeval>t_users:<noeval>[</noeval>DATA<noeval>]</noeval><noeval>]</noeval></textarea> Will pull back a list of users and show the current user.
<h2>Square Brackets and Server Variables</h2>
Here's a <a href="http://www.w3schools.com/asp/coll_servervariables.asp">full list</a> of server variables. You can also read more about <a href="http://dotnetjunkies.com/Tutorial/B3CCB57F-0BBD-4D68-96B0-480B2F1DFC28.dcik">Working with Server Variables</a>.">In Qrimp, you can use Square Brackets "[]" to create links. This is similar to functionality available in Wikipedia and WordPress. Square brackets can also be used to insert server variables and session values, display a field as a select box or radio button and more.
<h2>Creating Links with Square Brackets</h2>
The data in these Help Files is stored in a table called Help Topics. The current record has the Name "Square Brackets". To create a link to the Help Topics table type in <noeval>[Help Topics]</noeval>. To link directly to the Square Brackets record type in <noeval>[Square Brackets]</noeval>. You can also link to a record in a table with square brackets by using the name of the table followed by a colon and the record id, like this: <noeval>Square Brackets</noeval>.
<h2>Limitations of Square Brackets for Linking</h2>
If there are multiple records in your database with the same name or if the Name field is edited, your square brackets link will not work.
<h2>Links to Default Items</h2>
You can use square brackets in the HTML for links to make links more flexible and generic (see also [query string urls]). Normally when writing the HTML for a url, you would write &t=customers, but if you write <noeval>&t=HelpTopics</noeval> the link will go to the 'current' table or 'default' record. Links with square brackets are most commonly used in [Menus], the [Content Header], [Views] and [Field Templates]. For example, imagine this link: <img src="attachments/developer.qrimp.com-c71d519b-826b-48db-b481-5fcd427c9a14/linkwithsquarebrackets.png" /> is in the Content Header of my customers table. When I click the link, I will go to the create view (view 8) of the customers table. Square brackets pull in the closest item. <noeval>HelpTopics</noeval> will pull in the current table. <noeval>48</noeval> will pull in the current id.
<h2>Square Brackets to pull in DATA</h2>
Use <noeval>[DATA]</noeval> between the a tags to pull in the data from the linked record.
<h2>Square Bracket Select Lists</h2>
When displaying a form with a column or field that is a pick-from (drop-down), Qrimp displays a select box in HTML so the user can choose from the list. Behind this is a template. The template looks like this:
<textarea></textarea>
and produces this:
You can show a "friendly name" for items in the detail view, rather than the id number of the field. (See also [AutomaticIDs]
For example if you have an address table with a foreign key to the states table, when displaying the address, you'd like the state name to be displayed instead of the unique id for the state. To do this, you'd use something like the following:
[states:48] where 48 is the ID of the element to be displayed from the states table.
If this item is being displayed in the grid view, you can dynamically insert the Foreign key ID value into the square brackets like this:
<noeval>[states:[DATA]]</noeval> uses the data in the database for this cell to grab the state name. By default, Qrimp will display this notation in the web page with a link back to the detail page for the table and item specified by [DATA]. To show only the data without the link back to the item, use [states:[DATA]:nolink].
<noeval>[</noeval>t_users:<noeval>[</noeval>DATA<noeval>]</noeval><noeval>]</noeval></textarea> Will pull back a list of users and show the current user.
<h2>Square Brackets and Server Variables</h2>
Here's a <a href="http://www.w3schools.com/asp/coll_servervariables.asp">full list</a> of server variables. You can also read more about <a href="http://dotnetjunkies.com/Tutorial/B3CCB57F-0BBD-4D68-96B0-480B2F1DFC28.dcik">Working with Server Variables</a>. | Advanced Development |
| 50 | Drag and Drop | To use the drag-and-drop calendar, go to the calendar view of a table.
Now when you put your mouse over the mini calendar icon <img src="/attachments/d8b7ffcf-fc7d-4ec9-ae46-a5428e455455/CalendarIcon.png"> in front of the task name, the cursor will change to the move pointer, on Windows machines, it's usually a cross with arrows. On Macs, it's a little hand.
Click and hold this icon to drag the task to another day on the calendar. The due date for the task will automatically update to the day you dragged the task to.
| Calendar View |
| 51 | Design View | Design view or the Form Designer lets you re-arrange the location of the fields on the screen. Design view is used exclusively for the [Detail View] and [Edit View]
To design a view for a table, go to the table and view you want to change, then go to Design > Form Designer. Your page will reload with a dotted background. Click and hold the the field you want to move until a multi-directional arrow appears, then drag and drop it in its new location.
<img src="attachments/developer.qrimp.com-775afbce-4f82-42f1-b6da-650d2e4404e9/formdesigner.png" />
You can also move the "Save/Copy/Cancel" buttons that appear at the bottom of a page. | Views |
| 56 | Calendar View | All tables with a date field (Date/Time [datatype]) will be viewable on a Calendar. When the Calendar view is available, the calendar icon <img align="absmiddle" src="/attachments/d8b7ffcf-fc7d-4ec9-ae46-a5428e455455/CalendarIcon.png">appears near the top of the page in the [Content Header]. A link to the calendar view also appears by default in sub-menu tabs.
The Calendar View opens for the current month. You can select other months and years from the drop-downs at the top of the page. You can also go to a day view.
<h2>Tables with CreateDate and Other Date Fields</h2>
If you choose the [Auditing] option when creating your table, your table will be given a "createdate" field, which means that when you click on the Calendar View icon, you will see a calendar of when items were created.
If there is more than one Date field in a table, the Calendar will show items with the FIRST date column by default. Once you are in the Calendar, you will be able to choose to show the calendar with items from another date field using a drop-down box.
<img class="border" src="attachments/developer.qrimp.com-d2a90262-4623-4a26-8112-8eb1194a17ae/choosedateforcalendarview.png" />
For example, if I have a Projects table with two date fields "Start Date" and "End Date", when I first open the table in Calendar view, I will see items that Start on days in the month I am in. If I want to see when items end, I can choose "End Date" from the "View By" drop-down box at the top left of the view.
<h2>Changing Due Dates with Drag and Drop</h2>
You can [drag and drop] an item on the Calendar and the selected date field will update the item to the new date. | Views |
| 57 | Reports | Click the reports icon <img align=absmiddle src="/icons/crystal_project/32x32/mimetypes/spreadsheet_document.png" alt="Reports"/></a> to view reports.
You can also filter the reports by clicking the <img align=absmiddle src="/icons/crystal_project/32x32/actions/filefind.png" alt="Search"/> on the Reports page. | Data Analysis |
| 58 | Related Data | When you create one-to-many relationships between your tables, links to the related tables will appear by default on the right hand side of the page. If those related tables contain data, you will see the data rows listed.
<strong>Adding More Related Data</strong>
You may also see the option to add related data. For example, if I am in sub-project 2 and I wish to add another sub-project section, I click the "+ add..." and it takes me to a new sub-project, already configured to be related to sub-project 2.
<img class="border" src="attachments/0bd253ce-f5ff-4a48-b947-709b240c8ec6/RelatedDataHelp1.JPG" />
If you have enabled [Attachments] to your table, you will see a list of attachments with the related data.
Read more about [Adding Attachments].
Read more about [Headers and Footers]. | Above and beside your data |
| 59 | Enabling the Attachments Sidebar | To keep the size of your Qrimp application small, the attachments sidebar is not automatically visible on every table. If you would like to see attachments on the right in the [detail view] of each record, the table must be created with 'Attachments' enabled. If you do not see the attachments box to the right of your item, this option may not have been turned on. You can add it by following the instructions below.
<h2>Creating Tables with Attachments Sidebar Enabled</h2>
When you add a table, click the checkbox beside "Enable Attachments” in the Advanced options. (See [Add a Table]).
<img alt="Enable Attachments Checkbox" src="/attachments/105659f8-adf8-4c89-8658-69cfbd79a23a/Capture.PNG">
<h2>Enabling Attachments Sidebar for Existing Tables</h2>
If you created a table without the attachments sidebar capability (see above), you will have to add some code that will turn this feature on for a table.
First, go to the table you want to add the sidebar to. Then go to Develop > Header/Footer (see [Headers and Footers]). Copy and paste the following HTML into the footer section:
<textarea height="200px"><div id="attachmentsdata" class="relateddata"><h4><a href="db.aspx?t=attachments&tableid=1006&itemid=59" title="View All Attachments...">Attachments</a></h4></div><a href="uploadfile.aspx?t=1006&id=59&vid=11" id="Attachmentsadd" title="Upload Attachments...">add</a><script>showHtml("attachments&tableid=1006&itemid=59&vid=21&orderby=1,1","attachmentsdata");</script></textarea>
<em>Note: be sure to change the id number (tableid=1006 and t=1006) of the table to the id of your table. (See [automaticids]</em> | Attachments |
| 61 | Table Best Practices | <h2>Naming a Table</h2>
When you are creating a table, the table name should be the thing you want to keep track of, ie: "Customers", "Projects" or "TeddyBears". The name you choose should not have spaces and cannot be from the list of [Reserved Words].
<h2>Creating Fields</h2>
Fields are also known are records and are sometimes referred to as columns.
A Field name for the data we want to collect about our "thing" is the type of data we want to keep track of. For example, we may wish to store our TeddyBears Names, so we enter "Name" in the first field. Other fields we may want to keep track of would be FurColor, DateAcquired, Height, etc. The name you choose should not have spaces and cannot be from the list of [Reserved Words].
<h2>Selecting Field Data Types</h2>
Next we have to decide which type of data "BearName" is (see [datatype]). We choose which type of data this will be from the [datatype] drop-down list. If the datatype is a number, a date/time, or text, we choose from the first part of the drop-down list. BearName would be plain text, so we choose "Single-line text - 50 characters". We limit the length of the field to 50 spaces, because we don't want people to enter a lot of text in this field.
<h2>Creating from a Look-up Table</h2>
If we already have a table called 'FurColor' that lists colors of fur, we could choose furcolor from a [drop-down]/pick from/look-up table when we were choosing the [datatype] for the FurColor field. This would enable users entering data in the FurColor field to choose from a drop-down list of fur colors. You can choose these when the data entry options for a field already exist in a separate table. This will link the fields to other tables in your database. See [Look-up Tables]
<select name="datatype0"><option value='decimal(18, 2)'>Number with decimals</option><option value='int'>Number without decimals</option><option value='datetime'>Date/Time</option><option value='varchar (2)'>Text (2 characters)</option><option value='varchar (50)'>Text (50 characters)</option><option value=''>-- picklist from --</option><option value='pickfrom_accounttypes'>accounttypes</option><option value='pickfrom_t_furcolor'>furcolor</option><option value='pickfrom_t_users'>t_users</option><option value='pickfrom_t_views'>t_views</option><option value='pickfrom_yesno'>yesno</option></select>
| Building Your System |
| 62 | Import Data | If you have your data in excel spreadsheets, .csv files or tab delimited format, you can use the Data Import feature. This is a fast way of getting a large amount of data into your Qrimp database. Import data is also the best way to create [drop-downs].
To import data, simply copy/paste a spreadsheet or .csv file into a text box and Qrimp will create your table(s) for you. You can also type data into the text box.
To use the data import feature, go to Develop > Import Data. If you do not see an import data link, go to Develop > Create Tables, then click the Import Wizard link near the top of the page.
<img class="border" src="attachments/developer.qrimp.com-6d61a212-a8f6-491a-b559-066b0a28ed2f/importwizardlink.png" />
To make sure your table is created properly, use "clean" data and prepare it in a spreadsheet or text editor as follows:
Put the name of the column in the top row and all of the data to be in that column in the column below it.
The following data is in the correct format for import:
FirstName Position Salary
John Cashier $30,000
Christina Cashier $32,000
Note that if you are using a text editor, there should be tabs between each item (where the spaces are above).
When you are ready to import your data, copy and paste it into the text box, or upload a file. Check the box beside "First row contains field names", unless you are using the importer to [import data into an existing table].
<img class="border" src="attachments/developer.qrimp.com-e3da82e0-2ebe-4013-b682-973bf6346ddc/manufacturerslookup.png" />
<span class="small">Some simple data, ready to import.</span>
----
Choose Advanced Options.
If this is a lookup table, do not enable any advanced options.
----
Wait for the data to be imported.
<img class="border" src="/attachments/developer.qrimp.com-3a8cc099-9b5d-4a16-aa41-da62eab5a8bd/waitfortheimporttocomplete.png" />
----
You will see your newly imported data.
<img class="border" src="attachments/developer.qrimp.com-c42ec091-0af6-4a6a-9de5-c01eceeab695/newmanufactererslookup.png" />
---
Now you must:
Give permission to your users to see this table with [Table Security]. If you are adding this field to an existing table (if it's a lookup table), you will need to configure [Column Level Security].
<h3>Data Import Tips</h3>
<ol><li>Field names must start with a letter and can only contain letters, numbers and _. Spaces will be converted to _. </li><li>Data must be in tab-delimited format </li><li>If you want the data to be searchable, it needs a description field</li><li>Don't edit the data after you paste it, it will look funny because the lines wrap in the box, but it's okay, we'll take care of it. If you edit the data, your data table may not be created correctly.</li>
</ol>
If you have a name for your sheet, Qrimp will automatically name the data tables it creates with that name.
<strong>Multiple Sheet Import</strong>
You can import a spreadsheet file with multiple sheets. The data from each sheet will be created as a separate table, and relationships will be implied and constructed from and between your tables automatically.
| Building Your System |
| 63 | Entering and Editing Data | To enter data, go to the menu tab for your table, which appears on the top or left of your window. Hover over the tab, click the add icon in the sub-menu.
To edit records from the grid view, click the edit icon <img src="/images/edit.gif" /> next to the row you would like to edit.
To edit items from the [detail view] click the Edit icon <img src="icons/crystal_project/32x32/apps/kate.png" /> in the [Content Header].
Clicking on this icon takes you to the [Edit View]. | Working with Data |
| 64 | Rename Table | To rename a table, navigate to your table, then go to Develop > Manage Tables > Find your Table in the list.
Below the name field, there is a link "rename table". Click on it. Choose a new name for your table and click save.
<span class="error">Caution: Default sub-menus will be renamed to the name of the new table</span>
Rarely, but sometimes, renaming tables can be problematic if you have other tables or data in your application that refers to the table you want to rename. | Table Settings |
| 65 | Adding Related Data | You can add a link to the footer (#rightcontent) of a table that makes it easy to create a new related item (in a different table). One example of related data is the "add attachments" link that you see in almost every table.
In this example, let's say we wanted to be able to quickly look at Poll Numbers associated with that particular candidate. To do this, we'll add a link in the footer of the table.
<img src="/attachments/f8757fc8-aefe-4a8c-8c31-c27429dec238/AddRelatedData1.png">
| Related Data |
| 66 | Showing and Hiding Columns with the Query String | <h2><em>Advanced</em></h2>
You can edit a <a href="db.aspx?t=helptopics&id=261&vid=11">url</a> (link) to either show or hide columns in a table.
You can use the hide or select parameters to edit the url in your location bar and change the data that is displayed when you re-load your page.
You can also paste links to modified [query strings] into <a href="db.aspx?t=HelpTopics&id=10&vid=11">Menus</a>, the [Content Header], [Field Templates] and <a href="db.aspx?t=HelpTopics&id=176&vid=11">Views</a>.
<h2>Hidecolumns</h2>
Hidecolumns hides the columns you specify. If you are looking at a table and it is too long, and you don't want to add [custom views (sorting and filtering)], you can quickly edit the url to hide a few of these columns, but every time you refresh the page, you will have to re-hide them.
To hide a columns edit the url as follows:
"https://myapp.qrimp.net/db.aspx?t=mytablename&vid=11&hidecolumns=id,description,priorityid,partnershipagreement"
If you want to save this link as a menu tab, copy it from your location bar and type it into the linktemplate field of your menu tab (See [edit a menu tab]).
<em>Note: Hidecolumns retrieves all of the columns from the database and pulls them into your browser, then hides them from view. For this reason, hidecolumns uses more bandwidth than selectcolumns.</em>
<h2>Selectcolumns</h2>
You can retrieve and display only the particular columns you select using "selectcolumns" in your query string (url). A selectcolumns query is written as follows:
"db.aspx?t=tasklist&selectcolumns=name,duedate,priorityid"
Because showing columns only pulls back the selected columns, it uses less bandwidth than hiding columns. | Query Strings |
| 69 | focuson Query String Parameter | <nobreak><img src="/attachments/3ddae741-1e95-4629-8d44-879dbec2b8e1/focusonQueryString.png"><br/>
<p>When entering a url into the link section of a Menu tab, add a query string parameter called "focuson" with a value of the name of a field in your table to make the cursor appear in that field when the page loads.</p>
<h2>Example</h2>
<p>Your users would like to have the mouse cursor in the Name field when they load a Contacts form, so you add &focuson=name to the end of the query string on the menu that takes them there (see [Menus]).</p>
</nobreak> | Query String Parameters |
| 70 | Icontheme | Every Qrimp app comes with a set of icons built in. To view all of the icons included with your Qrimp app go to Design > Icons List.
<strong>Changing your default IconTheme</strong>
Each user can choose which icon size they wish to use in their [Content Header] when they are logged in. You can choose 16x16 icons or 32x32 icons.
To change a user's icon settings, go to [Admin] > [System Tables]. Find the t_settings table. You will see a list with all of your users in the userid column and their chosen icon style in the icontheme column. Click on edit icon in the row that contains the record you would like to change.
<img src="/attachments/506118aa-6f10-4062-ba05-bedbda15faf7/Picture 5.png">
When you are in [Edit View], go to the icontheme field and change the display size from 16x16 to 32x32 or vice versa.
<img src="/attachments/1ca1d94c-230b-4089-81fb-e41f742e6c41/Picture 4.png">
<strong>Using Your Custom Icons</strong>
Qrimp plans to allow you to add your own icon set if you wish. At the present time, this is only possible if you choose the same naming convention for your iconset as is used for the crystal project icons (names must include folders in url).
If Qrimp has 1000 icons in one set and 100 in another, and you set an icon to point to one of the 900 we don't have and change the icon theme, then they'll be broken images.
<strong>Using the Default IconTheme in your urls</strong>
When you are writing a url and want to link to an image automagically, you can use [square brackets] around the word icontheme to create a link to it in a url. Qrimp will automatically display whichever icon theme you have chosen. For example, [ /icons/crystal_project/32x32 ] will insert /icons/crystal_project/32x32. You must be sure to write the folder location for the urls correctly. Follow this example: /icons/crystal_project/32x32/actions/1day.png | Changing the Look and Feel of Your App |
| 72 | Drop a Column | To drop a column, first make sure that you are viewing the table that you want to drop the column from.
Next, go to Develop > Table Management. You will see a list of your tables with several options below each. Click the 'edit' option. You will see a form showing the fields in your table, with an options column on the right. Under the options column click the 'delete' link in the row for the column you wish to drop. You will be prompted before you can delete the column.
Be careful! Deleting a column will delete any data that you have entered into the table! | Manage Tables |
| 73 | Reserved Words | The following is a list of special words that are used in the code and cannot be used as the name of a table or column.
<ul><span id=reservedwords></span></ul>
<script>
getContent("db.aspx?t=reservedwords&vid=15&pagesize=800&noheader=true&nofooter=true","reservedwords",true);
</script> | Definitions |
| 74 | Edit View | Click the edit icon (<img alt="Edit Icon" src="/icons/crystal_project/32x32/apps/kate.png">) in the [Action Icons] area to edit records in your table. You will see each of the fields in your table shown as textboxes, radio buttons, drop-down lists etc. Enter or edit text, select options from drop-down list fields, change radio button options and dates and more. To save, click the save button at the bottom of the page.
<img alt="Edit View"src="/attachments/2603fbe4-4e91-43f7-a7df-b7d11c64c9c5/Edit View.png" /> | Views |
| 75 | Excel Export | You can export your data as an excel spreadsheet. Just look below your table when it is in grid view. Click the excel link to export!
<img src="/attachments/9075d7d3-3be5-43b0-bffc-a982ce70a068/ExportCSV_XLS.png"> | Export Options |
| 76 | XML Export | You can export your data tables in XML format. Go to the grid view of your data table. Below your table you will see Qrimp export options. Click on the XML link. You will see the document tree of your page in XML format. You can then copy and paste the XML and do whatever you please with it. | Export Options |
close