LISE & UDT - Get previous item's id

[WDJames]

Hi all,

I'm using a UDT to save the data from an enquiry form into a LISE instance. I need to give each enquiry an item ID which needs to be consecutive. On the UDT, is there a way to load the previous LISE item's "item_id" and add to it. Here is what I have in my UDT at the moment:

Code: Select all

$data = $params['formdata'];

$mod = cmsms()->GetModuleInstance('LISEEnquiry');
if(!is_object($mod))
return;

$obj = $mod->LoadItemByIdentifier('alias', $alias);

$obj->title = // I need the title to be the Item ID
$obj->alias = // The alias should be based on the title

// Rest of the code here

$mod->SaveItem($obj);

As always, thanks in advance for your help.

James

[Jo Morg]

Code: Select all

    # $db = \cms_utils::get_db(); // may or may not be needed...
  
    $query = 'SELECT MAX(item_id) FROM '
             . \cms_db_prefix()
             . 'module_' . $mod->_GetModuleAlias() . '_item';
    
    $last_id = (int)$db->GetOne($query);

That should work. Have fun!

[WDJames]

Hi Jo,

That is perfect. Thank you so much! I needed to uncomment the $db but it works as expected.

Thanks again,

James

[WDJames]

I've got a slight issue when an item is deleted (eg. Enquiry 4 with the item_id of 4) and then another is added. The name doesn't match the item_id (Enquiry 4 with the item_id of 5). Is there a way that instead of selecting the max value of the column that we just use the auto increment value?

[WDJames]

Hello, I've figured out a workaround for my particular issue - though I'm still open to suggestions if you have one.

Instead getting the item_id from the previous LISE item (or getting the highest value of the item_id column on the db as per Jo's suggestion), I've set up my UDT to first create a new LISE item using the form's submission id as an alias and name field then save the item then reload that same item and replace the title.

Code: Select all

$data = $params['formdata'];

$mod = cmsms()->GetModuleInstance('LISEEnquiry');
if(!is_object($mod))
return;

// LOAD OR CREATE ITEM

$alias = "enquiry-" . $data->get_field_value('cgbf_requestid');
$obj = $mod->LoadItemByIdentifier('alias', $alias);

$obj->title = $data->get_field_value('Name');
$obj->alias = $alias;
$obj->Name = $data->get_field_value('Name');
$obj->Email = $data->get_field_value('Email');

// SAVE ITEM
$mod->SaveItem($obj);

// LOAD ITEM & REPLACE DATA
$obj = $mod->LoadItemByIdentifier('alias', $alias);

$obj->title = $obj->item_id . " Request a quote";
$obj->alias = "enquiry_" . $obj->item_id;

// SAVE ITEM
$mod->SaveItem($obj);

I hope this makes sense and helps someone else.

Thanks,

James

[Jo Morg]

Well, usually everything depends on the job requirements and the reliability of an algorithm. Databases don't usually recreate ids based on whether you delete a record or not for a number of reasons:
1 - the counter auto-increments in a way that reliably generates UNIQUE ids;
2 - it's totally agnostic of any holes created by deleted records;
3 - which makes it simple to prevent duplicated ids

As an added bonus the process mimics the accounting numbering analogical systems in which accountants just invalidated previous issued documents but the next id was going to be the next of the sequence of already attributed ids, hence totally predictable.

Whether you are using one method or the other, just make sure you can rely on the system to generate unique ids, and don't accidentally reset the generator, which would eventually cause you troubles in the long run.

Another suggestion would be to generate some sort of more complex alphanumeric serial id (totally independent of the form request id or the item_id) with a timestamp inserted on it. I use these all the time to make sure that not only the IDs are unique but also that they are temporally traceable and still in a sequence that could be considered incremental, although not composed of consecutive +1 serials.

If needed, I'd still use an extra field in LISE to insert the form request id if you need to pair the submission to the LISE item.

In any case these are just suggestions, just go along with what works best for you and your requirements.

[WDJames]

Hi Jo, thank you for the feedback and clarification. I think I wasn't clear when I was explaining my requirements.

The client's request is for a consecutive id which is included in the item's title (their old system names requests/items this way - I suggested a complex alphanumeric serial id but they insisted - I was going to use the id generated by Smart Forms to keep it simple but tied in to the form submission).

I thought that the item_ids were perfect for their requirements (reasons are the same as per your feedback) and wanted to somehow "predict" the next item_id based on the previous item (by adding one to it). I did also try to use the auto increment value from the db but this didn't work like I hoped (I read somewhere that the auto increment value on the db updates every 24 hours).

I think my workaround is fine for this particular instance though I'm not really sure if there are any drawbacks that are caused by my code.

Once again, thanks for the feedback and suggestions - I really appreciate it as I'm sure I'll be back to this forum for more help.

Thanks,

James

[Jo Morg]

Absolutely, go with what works for you and your client. Babysit it for a while to see if there are any drawbacks, but if not, it's golden.
And you are welcome!