Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
configuration:lisanew:tableproperties [2019-10-23 07:31] Simon Jehrbo |
— (current) | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ==== Table properties ==== | ||
- | {{ : | ||
- | |||
- | You can also add a **descriptive expression**, | ||
- | |||
- | ==== Descriptive Expressions ==== | ||
- | |||
- | As described, Descriptive Expressions are SQL code. When typing them, the format is that you write the following | ||
- | |||
- | < | ||
- | SELECT --This is predefined and not visible | ||
- | |||
- | [company].[name]–this is what you modify | ||
- | |||
- | FROM [tablename] WHERE [idTABLENAME]= THE_ID_OF_THE_CURRENT CARD –This is predefined and not visible | ||
- | </ | ||
- | |||
- | If you need to do a subquery, you should have it within (). An example could be when getting something from the string table, to get the text from an option field. See the example below as well. | ||
- | |||
- | < | ||
- | SELECT --This is predefined and not visible | ||
- | |||
- | (SELECT [da] FROM [string] WHERE [idstirng] = [company].[buyingstatus] -- this is what you modify | ||
- | |||
- | FROM [tablename] WHERE [idTABLENAME] = THE_ID_OF_THE_CURRENT_CARD -- This is predefined and not visible | ||
- | </ | ||
- | |||
- | In the above example we get the Danish text from the option field Buying Status. | ||
- | |||
- | If you need to combine multiple things, you can do it by using the + sign. Just remember that you need to add whitespaces manually. | ||
- | |||
- | < | ||
- | [company].[name] + ' - ' + [company].[city] | ||
- | </ | ||
- | |||
- | The above example simply adds the Company name, then a whitespace a - another whitespace and then the city. | ||
- | |||
- | === Descriptive examples === | ||
- | |||
- | Here you can find code to descriptive expressions that we use often. This code can be a start point that you can modify when creating your own descriptive expressions. | ||
- | |||
- | __Company name as descriptive on an other card__ | ||
- | |||
- | Set company name as descriptive expression on a Role table (not on the company card, if you have an other table just change role to your table name): | ||
- | |||
- | < | ||
- | (Select[name]from[company]where[company].[idcompany]=[role].[company]) | ||
- | </ | ||
- | |||
- | __An option from a option list as descriptive__ | ||
- | |||
- | If you want to have a option from a option list as a dresciptive expression use the following code. This eg. gets the TypeOfRole from a Role table to be used as a descriptive expression. If you want to get an other language change sv to correct language. | ||
- | |||
- | < | ||
- | (Select[sv]from[string]where[string].[idstring]=[role].[typeofrole]) | ||
- | </ | ||
- | |||
- | __Two fields as descriptive expression__ | ||
- | |||
- | If you also want to have the company name and role as a descriptive expression, eg. & | ||
- | |||
- | < | ||
- | (Select[name]from[company]where[comany].[idcompany]=[role].[company])+' | ||
- | </ | ||
- | |||
- | Don't forget the brackets! | ||
- | |||
- | ==== Table properties ==== | ||
- | |||
- | ^ Name ^ Description | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | | '' | ||
- | |||
- | |||
- | ==== Record access ==== | ||
- | <WRAP center round important 60%> | ||
- | Be aware that Record Access has been replaced with [[configuration: | ||
- | </ | ||
- | |||
- | With the record access functionality it is possible to filter records the users have access to in a specific table. For example a salesman in Denmark will only have access to Danish companies while a salesman in Sweden will only have access to Swedish companies. | ||
- | |||
- | There are two different ways of setting record access. The first one involves building a query and the second one writing an SQL function. Building a query is the easiest way of setting record access, but when you need additional functionality or when you want to optimize performance a writing an SQL function is usually a better choice. To activate record access on a table the property Record Access must be set to Function or LIME Access Query. If it is set to None record access will be deactivated even if a query is built and/or a function is created. | ||
- | <WRAP round todo> | ||
- | === Example === | ||
- | The expression for Query for read access is similar to the WHERE-clause in an SQL statement. | ||
- | |||
- | Suppose we have a table called [company] with a relation to a table called [office]. In table [office] we have a field called [region] that specifies the location of the office. There is also a table calles [coworker] with a relation to [office]. If a user only is supposed to have access to companies in the same region as the user itself, the record access expression will look like this: | ||
- | |||
- | < | ||
- | |||
- | The expression activeuser is a keyword that will be replaced with the active user's coworker record. | ||
- | |||
- | Be aware that complicated queries will affect performance. The query will be executed every time a user tries to access data in the table. </ | ||
- | |||