How to create clone records from server side

[Manos Pappas]

Hello,

I have the following scenario where I need to create (almost) clone records on a table (only the record id and another field will be different). I am using SQL Server 2019 as the database.

I have a table called grow with the following fields:

- id (added by Jam, it is an integer autoincremented field - not shown to the user)

- afm (text)

- name (text)

- year (integer, lookup to another table called years, maps to field id which is integer auto-incremented).

From the server side, I have the following function to create clone records with different year value (the new lookup value already exists on the years table). The function is succesfully called from the client, the error appears on the server side.

def clone_grow(item, yearfrom, yearto):

       ...

       ...

   cgto = item.task.grow.copy()

   cgto.set_where(year=yearto)   # there is the possibility that there are no records already

   cgto.open(open_empty=True)

   cgfrom = item.task.grow.copy()

   cgfrom.set_where(year=yearfrom) # we have records

   cgfrom.open()

   while not cgfrom.eof():

        cgto.append()

        cgto.year.value = yearto     # the new record is for the new year id

        cgto.afm.value = cgfrom.afm.value

        cgto.name.value = cgfrom.name.value

        cgto.post()

        cgfrom.next()

    cgto.apply()

After executing the code, the following error appears:

https://gist.github.com/Frontier314/16729498ed7f547d02063f08ed510600

I am probably doing something stupid, can someone provide any hints why this is not working?

Many thanks in advance.

Best regards,

Manos Pappas

[Fabio Lenzarini]

Hi Manos, what's che primary key ot the table?

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/4bdb84c3-72e3-41d7-9517-0a214ec4f823n%40googlegroups.com.

[Dean D. Babic]

Hi,

I would check the client first with:

function on_view_form_created(item) {
    if (!item.lookup_field) {    
        var clone_btn = item.add_view_button('Clone', {image: 'icon-magic-wand'});
        clone_btn.click(function() { clone_record(item) });

...

function clone_record(item) {
    clone_fields = {};
    item.each_field(function(f) {
        clone_fields[f.field_name] = [f.value, f.lookup_value];
    });
    item.insert_record();
}
Looks like a FK problem.

[Manos Pappas]

Hi Fabio,

Table GROW has primary key the field ID (automatically added by Jam, it is type INTEGER auto-incremented) and table YEARS has again PK the field ID (auto-incremented integer by Jam).

[Manos Pappas]

Hi Dean,

The code cannot work in the client because the clone operation is performed from within another item.

If I try to use the posted code, the UI responds with the insert record dialog.

[Manos Pappas]

Hi again,

I've narrowed down the issue to a specific field; the field GROW.YEAR is integer but it is actually a lookup value to the field YEARS.ID.

For example, the ID value of year 2024 is 8, so I am trying to assign this value (8) to the GROW.YEAR field.

This somehow fails, thus the error.

If I do insert the values by hand from SQL Server Management tool, the record completes without errors, so the problem is not on the FK constraint that the table GROW has.

[Manos Pappas]

Hi,

One question: how someone can set the index value of a lookup field from server side?

For the above example (table GROW has field YEAR which is type INTEGER and lookups to table YEARS and field ID), I have a valid record with YEARS.ID = 8 and want to set this value (8) to GROW.YEAR.

No matter what I try (tried grow.year.value = 8, grow.year.lookup_value = 8) and all of these fail.

Any ideas?

Many thanks in advance,

Manos

[Dean D. Babic]

Hi Manos, 

I am sort of cloning the records here into the position log table:

https://positionstracking.pythonanywhere.com/

Not sure if this helps, but the export is there...

[Dean D. Babic]

Just added Test(INT) for POsition Log, and it is working:

from datetime import date, timedelta, datetime

def on_apply(item, delta, params, connection):
    positionlog = item.task.positionlog.copy()
    positionlog.open()
    changes = {}
    delta.update_deleted()
    # print(delta)
    for d in delta:
        for t in d:
            changes[t.positionid.value] = t.position.value
            changes[t.staffid.value] = t.staffid.value
            changes[t.startdate.value] = t.startdate.value

    positionlog.append()
    positionlog.positionid.value = changes[t.positionid.value]
    positionlog.staffid.value = changes[t.staffid.value]
    positionlog.positionstartdate.value = changes[t.startdate.value]
    positionlog.test.value = 1
    positionlog.post()
    positionlog.apply()
       
        

[Manos Pappas]

Hi Dean,

Will check this out, thank you!

[Manos Pappas]

Hi Dean,

test(INT) does not lookup to position.id. 

If we do the definition as positionlog.test -> position.id, the code works but does not insert the test value to the positionlog table.

I also think that this test is not valid because the database is SQLite and not MSSQL, I have a (bad) feeling that this is also db related.

[Manos Pappas]

Hello,

After many tests and with the help from Dean, I've managed to pinpoint the issue.

When using MSSQL (pymssql) as the DB backend, if one has to clone records, a first() method should be used before actually iterating the source data.

So, here is the example that worked (Python, server module):

def clone_grow(item, yearfrom, yearto):

       ...

       ...

   # cgfrom = the source dataset

   # cgto = the destination dataset

   cgto = item.task.grow.copy()

   cgto.open(open_empty=True)

   cgfrom = item.task.grow.copy()

   cgfrom.set_where(year=yearfrom) # we have records

   cgfrom.open()

   cgfrom.first()   # this is needed when using pymssql

   while not cgfrom.eof():

        cgto.append()

        cgto.year.value = yearto     # the new record is for the new year id

        cgto.afm.value = cgfrom.afm.value

        cgto.name.value = cgfrom.name.value

        cgto.post()

        cgfrom.next()

    cgto.apply()