Hi Laura,

That's odd. I pretty much ran the same query on a list without issue. The output shows what records got deleted when I run it:

=========
> deldata2 <- labkey.deleteRows(
+ baseUrl="https://www.labkey.org",
+ folderPath="/Support Tickets/Dashboards/JonY",
+ schemaName="lists",
+ queryName="ListTest",
+ toDelete=data.frame(Key=c(2,3,4), stringsAsFactors=FALSE)
+ )
> deldata2
$rowsAffected
[1] 3

$queryName
[1] "ListTest"

$schemaName
[1] "lists"

$containerPath
[1] "/Support Tickets/Dashboards/JonY"

$rows
$rows[[1]]
$rows[[1]]$EntityId
[1] "0ac6dd24-bca7-1032-80ef-e5ac46246e24"

$rows[[1]]$Check
[1] FALSE

$rows[[1]]$Key
[1] 2

$rows[[1]]$Name
[1] "Frank"


$rows[[2]]
$rows[[2]]$EntityId
[1] "f5af4afa-bf8a-1032-80ef-e5ac46246e24"

$rows[[2]]$Check
[1] TRUE

$rows[[2]]$Key
[1] 3


$rows[[3]]
$rows[[3]]$EntityId
[1] "26e52ba1-d663-1032-b44e-e5ac46241b3e"

$rows[[3]]$Check
[1] FALSE

$rows[[3]]$Key
[1] 4


$command
[1] "delete"
>
=======

So that $rows section of yours should show the actual records being deleted rather than list().

Is that Peptide field the Primary Key for your list?

Regards,

Jon

Hi,
No, peptide is not a primary key. Is that a requirement? It doesn't say so in the documentation. Also, it doesn't seem that I can make peptide a primary key; there is no option to have a non-integer primary key in the list design interface. If I try to use the default primary key (from the list design) called 'Key', that doesn't work either.

labkey.deleteRows(baseUrl="https://labkey-dev.roche.com:8443/labkey",
                  folderPath="/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results",
                  schemaName="lists",
                  queryName="QuaSAR_AuDIT",
                  toDelete=data.frame(Key=c(1:3000),
                                      stringsAsFactors = FALSE)
                  )

$rowsAffected
[1] 3000

$queryName
[1] "QuaSAR_AuDIT"

$schemaName
[1] "lists"

$containerPath
[1] "/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results"

$rows
list()

$command
[1] "delete"


Can you tell me the specific list design requirements that would allow labkey.deleteRows to work?

Sorry, what I mean to ask was whether toDelete requires a primary key in the data frame. Per my last response, a primary key is not working for my case.

Hi Laura,

For the deleteRows function to work, it has to at least use whatever would be the primary key for that table.

Can you go into the List Designer for that specific list and see what field is being used as the Primary Key value and whether it's an integer or a string value?

Regards,

Jon

Hi Jon,
The primary key is the default integer 'Key' and that does not work in toDelete when I try it. The list designer does not allow me to change the primary key to a string. I've tried just about everything. Are there any undocumented rules that I need to follow to allow toDelete to work?

Hi Laura,

No, that should be it. If it's Key, then your query to delete is right.

Just curious, are you able to run a selectRows query and get results to view?

testSelectRows <- labkey.selectRows(
                  baseUrl="https://labkey-dev.roche.com:8443/labkey",
                  folderPath="/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results",
                  schemaName="lists",
                  queryName="QuaSAR_AuDIT"
                  )

Also, have you tried to do a delete on a single row like this?

testDeleteRows <- labkey.deleteRows(
                  baseUrl="https://labkey-dev.roche.com:8443/labkey",
                  folderPath="/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results",
                  schemaName="lists",
                  queryName="QuaSAR_AuDIT",
                  toDelete=data.frame(Key=1, stringsAsFactors = FALSE)
                  )

Regards,

Jon

Hi Jon,
Nope, labkey.deleteRows is not working. labkey.selectRows has always worked fine. I am using up to date packages and running from a Mac. Any ideas?

> todelete <- labkey.deleteRows(baseUrl="https://labkey-dev.roche.com:8443/labkey",
+ folderPath="/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results",
+ schemaName="lists",
+ queryName="QuaSAR_AuDIT",
+ toDelete=data.frame(Key=1,
+ stringsAsFactors = FALSE)
+ )
> todelete
$rowsAffected
[1] 1

$queryName
[1] "QuaSAR_AuDIT"

$schemaName
[1] "lists"

$containerPath
[1] "/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results"

$rows
list()

$command
[1] "delete"



> toselect <- labkey.selectRows(
+ baseUrl="https://labkey-dev.roche.com:8443/labkey",
+ folderPath="/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results",
+ schemaName="lists",
+ queryName="QuaSAR_AuDIT"
+ )
> str(toselect)
'data.frame':    3888 obs. of 14 variables:

Hi Laura,

I'm running on a Mac as well using the Rlabkey package 2.1.129 and running on R 3.2.3

Just curious, but do those Key values exist?

I managed to reproduce the same output from R, but only if I provided it with a Key value that was not on my list.

So if I had Key values 1 through 6 and I used a value of 10, it would show me $rows with list() rather than the content it deleted.

Example:

> deleterow <- data.frame(Key=10)
> deldata <- labkey.deleteRows(
+ baseUrl="https://www.labkey.org",
+ folderPath="/Support Tickets/Dashboards/JonY",
+ schemaName="lists",
+ queryName="ListTest",
+ toDelete=deleterow
+ )
> deldata
$rowsAffected
[1] 1

$queryName
[1] "ListTest"

$schemaName
[1] "lists"

$containerPath
[1] "/Support Tickets/Dashboards/JonY"

$rows
list()

$command
[1] "delete"


==============

> deleterow <- data.frame(Key=5)
> deldata <- labkey.deleteRows(
+ baseUrl="https://www.labkey.org",
+ folderPath="/Support Tickets/Dashboards/JonY",
+ schemaName="lists",
+ queryName="ListTest",
+ toDelete=deleterow
+ )
> deldata
$rowsAffected
[1] 1

$queryName
[1] "ListTest"

$schemaName
[1] "lists"

$containerPath
[1] "/Support Tickets/Dashboards/JonY"

$rows
$rows[[1]]
$rows[[1]]$EntityId
[1] "ac46c1e0-b18a-1033-9005-a3afb15921c0"

$rows[[1]]$Check
[1] FALSE

$rows[[1]]$Key
[1] 5

$rows[[1]]$Name
[1] "Joe"



$command
[1] "delete"



Notice how the first example using the Key value of 10 looks completely different than the other one with the Key value of 5?

I'm starting to think that the records you're trying to delete were already deleted, which is why this R script isn't working.

Regards,

Jon

Hi Jon,
The rows are definitely there. I even populated Key with integers myself. 'Key' is actually invisible to the default view, but it's definitely in the columns list. I attached screenshots of the list design and schema browser.

I started from scratch by manually deleting the rows using 'Delete All Rows' in the schema browser. Then, I ran the code below. Inserting rows works like a charm, but I cannot delete them.

.......other code before this part....
import_AuDIT$Key <- seq_along(import_AuDIT$peptide)

labkey.insertRows(baseUrl = "https://labkey-dev.roche.com:8443/labkey",
                  folderPath = "/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results",
                  schemaName = "lists",
                  queryName = "QuaSAR_AuDIT",
                  toInsert = import_AuDIT)

labkey.deleteRows(baseUrl="https://labkey-dev.roche.com:8443/labkey",
                  folderPath="/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results",
                  schemaName="lists",
                  queryName="QuaSAR_AuDIT",
                  toDelete=data.frame(Key=1:3888,
                                      stringsAsFactors = FALSE)
                  )

$rowsAffected
[1] 3888

$queryName
[1] "QuaSAR_AuDIT"

$schemaName
[1] "lists"

$containerPath
[1] "/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results"

$rows
list()

$command
[1] "delete"


I think there's something wrong with the primary key and I don't know what it is. I tried to select via the Key, but that returns an data frame with columns but 0 observations.

testselect <- labkey.selectRows(
  baseUrl="https://labkey-dev.roche.com:8443/labkey",
  folderPath="/gRED/QuaSAR Dev 2/QuaSAR Dev 2 results",
  schemaName="lists",
  queryName="QuaSAR_AuDIT",
  viewName="",
  colNameOpt = "fieldname",
  colFilter=makeFilter(c("Key", "IN", "1")),
  containerFilter=NULL
)

> str(testselect)
'data.frame':    0 obs. of 14 variables:
 $ peptide : num
 $ sample : num
 $ transition.id: num
 $ pvalue.final : num
 $ status : num
 $ final.call : num
 $ SkylineNumber: num
 $ Category : num
 $ Panel : num
 $ Conc : num
 $ ConcLevel : num
 $ protein : num
 $ cv : num
 $ cv.status : num

Hi Laura,

This is really strange. There is nothing special that needs to be done to delete the record here.

I'd like to confirm a few other things with you.

1. Can you delete records in the QuaSAR_AuDIT list directly?

2. Can you try to delete a record using the Javascript API?

Use the following code below in a wiki page in the same folder as the list and replace KEY_NUMBER with one of the Key values you want to remove.

==================================

<p><button onclick="onSubmit()">Click me to delete a record</button></p>

<script type="text/javascript">

function onSubmit(){
LABKEY.Query.deleteRows({
    requiredVersion: 9.1,
    schemaName: 'lists',
    queryName: 'QuaSAR_AuDIT',
    rows: [{'Key': KEY_NUMBER}],
    success: onSuccess
});

function onSuccess(results) {
                  alert('Success!');
                 }
}
</script>

==================================


Regards,

Jon

Hi Jon,

1. Can you delete records in the QuaSAR_AuDIT list directly? Yes, I can delete selected records or all records through the schema browser GUI.

2. Can you try to delete a record using the Javascript API? I tried this and it won't work with a Javascript API; no errors either.

Hi Laura,

Interesting. Can you do one more thing with regard to the Javascript API one?

If you're using Google Chrome, can you bring up the wiki page you made, then bring up the Developer Console and go to the Network section?

After you get there, click that button and you should see an entry called "query-deleteRows.view". If you click it, it should being up a side panel showing Headers and Response tabs.

From the bottom of the Headers tab, you should see a Request Payload section that should show your delete request as a JSON response with the Key value you wanted to remove along with the schema and query names. (See screenshot)

On the Response tab, if things worked, it should be showing a JSON response like this indicating what got deleted:

{
  "rowsAffected" : 1,
  "queryName" : "ListTest",
  "schemaName" : "lists",
  "containerPath" : "/Support Tickets/Dashboards/JonY",
  "rows" : [ {
    "EntityId" : "5de988d5-b275-1033-b8c3-a3afb159e818",
    "Check" : false,
    "Key" : 8,
    "Name" : "Joe"
  } ],
  "command" : "delete"
}

Regards,

Jon

Hi Jon,
See screenshot. The single row did not delete. Your help is much appreciated.

Hi Laura,

What about the response tab? Was it blank or does it show a JSON response inside?

Regards,

Jon

Sorry about that. I didn't include the 2nd screenshot last time. It is attached. The rows are empty like the R script.

Wow! It looks like it's dropping the values within the row before it actually commits the changes.

I think I may need to make a bug on this.

Can you tell me what version and build of LabKey you're running?

Also, am I correct in thinking that this behavior you're seeing affects all lists you have?

Regards,

Jon

Hi Jon,

LabKey 15.3. Yes, I've tried this on a few of my lists and the behavior is all the same.

Regards,
Laura

Hi Laura,

Can you provide me with the build number as well? You should be able to find it under the Admin Console and expanding the + sign for Core under the Module Information section.

Regards,

Jon

Hi Laura,

I worked with Jon on this issue directly and found no problem. That QuaSAR_Audit table's earliest record was in the 20,585 for the primary key.

We managed to run the same javascript test successfully via that button code I provided you and confirmed the record deleted properly. So the R-code issue where the deleteRows wasn't working was in-fact due to requesting to delete a record that didn't exist.

Regards,

Jon

Hi Jon,
I'm still not clear on what to do about the Key. I reset the Key values myself. Is this just now allowed? I don't get any error or warnings. It seems that updating the primary key is not allowed, nor am I able to query the values of the primary key via R script. How should one go about this?

Hi Laura,

On those lists, if you have the "Auto-Increment Integer" option enabled when the list is first created, updating the Key to something else is not possible.

The only way a list will allow the Key to be edited is when you specifically pick Integer or String when you first create the list instead of the "Auto-Increment Integer" option.

Regards,

Jon

Also, if you need to see the Key value appear in your R query, you can add the attribute "showHidden=TRUE" in your query.

That will allow the Key value (which is normally hidden) to appear as a value in your query's results.

Regards,

Jon

Hi Jon,
I'm still not able to do this. See the list design snapshots from my response on 2016-02-10 12:57. I was questioning the list design for the Key earlier, but we ended up looking at another potential related issue with the server and then you did some troubleshooting with Jon Sasano. I don't see any "Auto-Increment Integer" option anywhere in the list design. Actually, I looked for something like that a whole back and don't see anything that resembles that option. Also, as you can see from my snapshot, there is no option for me to specify a non-integer Key....it is grayed out and not letting me choose.

Hi Laura,

The Auto-Increment only appears when you initially create a list (see first attachment called "autoincrement.png").

Once you get past that initial page, you can never go back and change it.

This means that your lists cannot have the primary key changed to a different value. If the Primary Key Type was set to Integer from the start,

I've provided you with screenshots of two different lists, one using Integer as the Primary Key Type and the other list using the Auto-Increment Integer as the Primary Key Type.

As you can see, if the Auto-Increment option is used the moment the list is made, you will never get the option to edit that Key value in the UI or any place else. If the regular Integer option is picked at the moment of creating the list, then you get the box that allows you to change the Key value to whatever you want.

This is why I believe your lists were created using the Auto-Increment option. This is also the reason why your attempt at trying to update the Key value on all those records in the list didn't work (because the Key values were not editable to start) and why the deleteRows option wasn't working (due to trying to delete Keys that were invalid).

Does this make sense?

Regards,

Jon

Hi Jon,
Thanks for all of your help & clarifications. I am able to delete rows after fixing my list designs.

Regards,
Laura

Fantastic! Glad to know it's working for you now!

Regards,

Jon