• Sign In
  1. Messages

List design and labkey.selectRows

LabKey Support Forum (Inactive)

View Message

view list print
List design and labkey.selectRows laurapas  2016-02-23 12:49
Status: Closed
 
Hi,
As a follow up to the forum thread that I submitted a couple of weeks ago (titled labkey.deleteRows), I redesigned my lists so that the primary key would be an Integer that I can specify it myself instead of the Auto-integer. One problem I see now is that the resulting R labkey.selectRows is resulting in a duplicate primary key column. See attachment snapshots for my list creation. Here is the labkey.selectRows that I am using.

peptideref <- labkey.selectRows(
  baseUrl="https://labkey-dev.roche.com:8443/labkey",
  folderPath="/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results",
  schemaName="lists",
  queryName="peptidereference",
  showHidden = TRUE,
  colNameOpt = "fieldname",
  containerFilter=NULL
)

> str(peptideref)
'data.frame':    67 obs. of 8 variables:
 $ Skyline Number: num 1 2 4 5 6 7 8 9 10 11 ...
 $ Category : chr "Spike" ...
 $ Panel : chr "V1" ...
 $ Conc : chr "M" ...
 $ Conc Level : chr "M11" ...
 $ Protein : chr "P68082" ...
 $ Peptide : chr "HGTVVLTALGGILK" ...
 $ Skyline Number: num 1 2 4 5 6 7 8 9 10 11 ...


I tried showHidden = FALSE and it does not make a difference; I get the same duplicate primary key. Is there something I am missing?
 
 
Jon (LabKey DevOps) responded:  2016-02-23 21:12
Hi Laura,

That's really strange. I couldn't reproduce the problem when I created a list using an integer for my Key (not the auto-increment kind).

Does this duplicate field appear when you access any list or just the one?

Can you run the same query against our LabKey.org site using this list (I've provided you access to a special folder that has a special integer list created):

listtest <- labkey.selectRows(
  baseUrl="https://www.labkey.org",
  folderPath="/Support%20Tickets/TestLists",
  schemaName="lists",
  queryName="RocheSkylineListTest",
  colNameOpt = "fieldname",
  containerFilter=NULL
)

This should bring up just three fields if you run a str(listtest) against it and you should only see three columns of data.

Regards,

Jon
 
laurapas responded:  2016-02-24 13:51
Hi Jon,
I seems that I don't have access to the Support Tickets folder and can't complete your test. I don't see this folder when I log in and I get an "unauthorized" warning in R.

I've tried just about every test combo I could think of. So, I believe I've found the cause of my undesired results, but not sure if the LabKey behavior is intended or needs to be reported as an issue. I get the duplicate primary key columns when reading the data (e.g., getRows or labkey.selectRows) and the name of the primary key contains a space (i.e., "Skyline Number"). I noticed that the field turns yellow when I have a space in a name (see snapshot)......is that supposed to be some kind of warning? I can use spaces in other non-primary key names and don't run into any problems. I'll just remove spaces from names. It's better not to have the spaces in the column names anyway; i can put these in the labels.

Regards,
Laura
 
Jon (LabKey DevOps) responded:  2016-02-24 13:57
Hi Laura,

Yes, that yellow is a warning since generally you should in best practice keep your field names as one word.

So rather than "Skyline Number", you should use a convention like "skylineNumber", using camelCase text instead.

Also, can you access this list?

https://www.labkey.org/Support%20Tickets/TestLists/list-grid.view?listId=2

I've purposely setup access to that list to where only LabKey personnel and you with your current login can access it.

Regards,

Jon