Hi Leo,

Can you give us some more details as to what you're looking to do?

When you say "unique values for a certain field", are you asking that someone can only insert in a single unique value that is not already in use (like a unique primary key) or are you asking if someone can only insert a preset series of values?

Regards,

Jon

Jon,

I'd like to hear if there is a way to only allow enumeration (insert a preset series of values), though this was not my intent.

I am after exactly the "primary key" restriction (insert something only if not already present), except a primary key already exists, so this is more like the UNIQUE keyword in traditional SQL.

FYI: PK is an integer and the field I'd like to have the UNIQUE constraint on is a string.

Thanks.
-Leo

Hi Leo,

If you need to have one column with a preset series of values, you could do the following:

1. Create another list
1a. Rename the Primary Key to what you'd like the field to be called
1b. Select the Primary Key Type as String.
1c. Click Create List
2. Add the preset values you want in that list under that one single column
3. Create another list and add a lookup to that first list, enable the validation lookup and make it required.

The new list will lookup the other one and only give the end-user a specific preset number of options to use for the one column based on the other list.

With regard to trying to have a behavior similar to the primary key restriction on another column, this functionality is unfortunately not available in lists as-is from what I can see. However, you could create a Trigger Script module to where it will only allow you to insert new rows provided the value isn't being used. This would only work on individually inserted records though and not when you import data via a copy/paste of TSV data.

https://www.labkey.org/wiki/home/Documentation/page.view?name=serverSideValidation

The trigger script would have to use the beforeInsert(row, errors) function to trigger just before the insert was attempted. Then, it would have to read the row.FIELD you were inputting and perform an IF/ELSE statement where IF the value of the field you're inputting equals to any of the existing values in that same field, generate an error; else insert the new record.

Regards,

Jon

Ok, thanks, Jon. Nice approach with enumeration.

More questions on lists.
Is it possible to apply custom views to them?
Like how it's described here: https://www.labkey.org/wiki/home/Documentation/page.view?name=addCustomQueryView

I have a file located at <moduleName>/queries/lists/Studies/test.qview.xml with contents
<customView xmlns:cv='http://labkey.org/data/xml/queryCustomView'>
    <columns>
        <column name='name'/>
        <column name='id'/>
    </columns>
    <sorts>
        <sort column='id' descending='true'/>
    </sorts>
</customView>

The list definition contains fields named 'name' and 'id'.
Its name is "Studies".

But I'm not seeing the custom view 'test' in the drop-down for the Views.

I can apply, of course, a UI based sort to the specified column via the "Customize View" UI.

I also have a <moduleName>/queries/lists/Studies.query.xml file with contents:
<?xml version='1.0' encoding='UTF-8'?>
<query name='Studies' schemaName='lists' xmlns='http://labkey.org/data/xml/query'>
    <description>
        List all of the studies available starting with 'SDY'.
    </description>
</query>

But that description does not show up next to the list in the Query Browser, may be, it's not supported?


By the way, speaking of lists fields in particular (I haven't verified it for other cases, such as dataset definitions), I noticed 2 behaviors, which are, I felt, inconvenient/confusing to the user (they were to me at least).

1. when specifying a RegEx validator, one clicks the "Add..." button, a pop up shows up prompting to enter details, one starts to enter them, then hovers the mouse pointer over the question mark next to the "Regular Expression*" to get an idea about the flavor/syntax of the RegEx used, where one sees the "Pattern" link to the Java's class, but after clicking it, opening it in a new tab/window, the pop up with all of the information entered for the RegEx validator disappears, clicking the "Add..." button again bring up a new/empty pop-up

2. once there is a RegEx validator in place, when examining it: clicking the field, then the "Validators" tab, immediately there are no UI elements for editing/deleting the RegEx; one has to click on a different field (if one is present), then back to the field in question, then the 2 icons for editing and deleting show up. A screenshot of them not showing up is attached.

Hi Leo,

I was able to use the following XML without any issue after restarting Tomcat:

<customView xmlns="http://labkey.org/data/xml/queryCustomView">
    <columns>
        <column name="Name" />
        <column name="Number" />
    <column name="Key" />
    </columns>
    <sorts>
        <sort column="Key" descending="true"/>
    </sorts>
</customView>

This successfully came up as a View option along side the default for that specific list I was looking at. Maybe you just need to remove the :cv part and just have the equal sign instead for the first line?

Also, a .query.xml file is to be used only on tables that are being generated by .sql files and not pre-existing queries. It is explained in the following page after addCustomQueryView (https://www.labkey.org/wiki/home/Documentation/page.view?name=addSQLQuery)

"If supplied, the metadata file should have the same name as the .sql file, but with a ".query.xml" extends (e.g., PeptideCounts.query.xml) (docs: query.xsd)"

The two RegEx questions:

Regarding the first RegEx question, that is definitely a bug that is overshooting it's close function that I'll need to get filed.

Regarding the other RegEx question where the validator section where the X to delete it doesn't come up, I believe this might be function by design since the only time the option to get the X appears is when an update is going to occur on that tab. I'll submit it as a separate bug to be looked at.

Regards,

Jon

Tooltip RegEx bug - https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=24682

RegEx Edit bug - https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=24683

Jon,

Thanks for catching the ":cv" part - that was it, it's now working even without restarting the server; I originally had "cv"s all over the place, but search-and-replace left this one stray one, and it took your fresh look from the outside to see it.

Got you about the .query.xml file - removed it.

You even simplified the first RegEx bug, that's great.

Regarding the 2nd one, even if it's by design, I say, it's a poor design. It's not just the delete X option that's missing, but also the edit icon (as you properly noted in the bug report itself); it doesn't show up right away, unless you
1) add another RegEx
2) toggle the Required check-box or
3) click on a different field and then come back to the first one

Imagine you're coming back there just to tweak the one RegEx you have, you have to jump through some hoops to do it...

Hi Leo,

Just to let you know that we were able to get this fixed in the upcoming 16.1 release of LabKey that will be available in March.

However, if you need this sooner, we did push this change to our trunk build (r42586).

Regards,

Jon