Insert into reference:list not working - FOREIGN KEY constraint failed field:upload

[LoveWeb2py]

Hello, I'm trying to create a simple interface that allows users to select multiple files and create a name for the bundle. I've followed the example in the book but keep getting the same error... FOREIGN KEY constraint failed.

When I look in all_files, it has the keys available. 

I tried following this example: http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#listtype-and-contains

I am wondering if it has something to do with the filetype being an upload field.

I've even tried getting the reference object id by doing 

s = db(db.all_files.id>0).select()
file_ids = [x.id for x in s if int(x) in all_files]

name = 'test_group_name'
file_ids = [1L, 2L, 3L]

db.file_groups.insert(group_name=test_id, filename=file_ids)

Function argument list

(self=<pydal.adapters.sqlite.SQLite object>, table=<Table file_groups (id, group_name, filename)>, fields=[(<pydal.objects.Field object>, [27, 28, 29, 33, 34, 31, 30, 32, 35, 36]), (<pydal.objects.Field object>, 'filegroup1')])

db.define_table('all_files',
 Field('filename','upload', unique=True),
 Field('uploaded_by', db.auth_user),
 format="%(filename)s")

db.define_table('file_groups',
 Field('group_name'),
 Field('filename', 'list:reference all_files'))

Sincerely appreciate any help...

[Anthony]

Please show the full traceback.

[LoveWeb2py]

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14.

Traceback (most recent call last): File "/Users/me/web2py/gluon/restricted.py", line 219, in restricted exec(ccode, environment) File "/Users/me/web-dev/web2py/applications/myapp/controllers/filegroups.py", line 88, in <module> File "/Users/me/web2py/gluon/globals.py", line 414, in <lambda> self._caller = lambda f: f() File "/Users/me/web2py/applications/myapp/controllers/filegroups.py", line 80, in test_1 db.file_groups.insert(group_name=test_id, filename=file_ids) File "/usr/local/lib/python2.7/site-packages/pydal/objects.py", line 734, in insert ret = self._db._adapter.insert(self, row.op_values()) File "/usr/local/lib/python2.7/site-packages/pydal/adapters/base.py", line 486, in insert raise e IntegrityError: FOREIGN KEY constraint failed

On Sunday, October 1, 2017 at 6:40:11 PM UTC-4, Anthony wrote:

Please show the full traceback.

[LoveWeb2py]

481. 482. 483. 484. 485. 486. 487. 488. 489. 490.

Here is the code listing self.execute(query) except: e = sys.exc_info()[1] if hasattr(table, '_on_insert_error'): return table._on_insert_error(table, fields, e) raise e if hasattr(table, '_primarykey'): pkdict = dict([ (k[0].name, k[1]) for k in fields if k[0].name in table._primarykey])

[LoveWeb2py]

Anyone have feedback? I have been banging my head against this all day...

[Anthony]

Was that field originally a regular reference field and then you changed it to a list:reference field?

[LoveWeb2py]

Yes... I believe so.

[Anthony]

On Monday, October 2, 2017 at 7:57:19 PM UTC-4, LoveWeb2py wrote:

Yes... I believe so.

On Monday, October 2, 2017 at 7:53:13 PM UTC-4, Anthony wrote:

Was that field originally a regular reference field and then you changed it to a list:reference field?

I think that's the problem. Try re-creating the table (save the data to re-import).

Anthony

[LoveWeb2py]

Okay, just delete the table and then set migrate=True ? 

[LoveWeb2py]

Still receiving same error...

This is my html code:

I'm taking values from jstree and then putting them into a database (trying to at least)

In view

    $("#btn").click(function () {
          var checked = $('#tree').jstree(true).get_selected();
          var testid = $('#test_id').val();

          console.log(checked)
          ajax('{{=URL('test_1')}}' +'?checked=' + checked + '?test_id='+testid, [], ':eval');
    });
}

and my test_1 function

controller

def test_1():
    value = request.vars.checked
    record_ids = [long(s) for s in re.findall('var_([0-9]*)', value)]
    test_id = re.findall('\?test_id=(.*)', value)[0]

    file_ids = [x.id for x in s if int(x) in record_ids]

    db.file_groups.insert(group_name=test_id, filename=file_ids)
    session.test = file_ids, test_id

    # handle the variable
    redirect(URL('run', args=value), client_side=True)

[LoveWeb2py]

Would it be easier to create a name field instead of a file and then just reference the name ID, and pull the file from the associated table?

[Anthony]

On Monday, October 2, 2017 at 9:24:29 PM UTC-4, LoveWeb2py wrote:

Still receiving same error...

Maybe you didn't properly delete the table. No foreign key constraint is set for a list:reference field, so if you are getting that error, that means the database still has a foreign key constraint on the field, which implies the table was not deleted and re-created.

Anthony

[LoveWeb2py]

I tried just renaming both databases and still received the same error.

[LoveWeb2py]

I moved all of the table files to an archive directory and let everything rebuild and it worked great. Thanks, Anthony! Really appreciate the time you take to help us all.