Internal ID display

[bespokeappstt]

My form, list view and details is taking shape. I now have a need to change how the internal id looks. This auto_increment number, can I change it to look like 00003 or anything close. I am trying to get away from the 3 look on list and details view. It doesn't look to good. I'm thinking might be a good idea to do this at the database level but I'm not sure if it will affect the code. Go back into mysql and define a starting number or something of the sort, 1000. So the next first entry will be 1001. What can be done? I want to change that single digit to read 6 digits ideally.

 

[cheesegrits]

You can't mess with the internal ID.

However, you could create a calc element which does something like ...

return sprintf('%06d', (int)'{yourtable___id}');

... and set it to 'calc on save only' No.

-- hugh

 

[bespokeappstt]

The plugin will be a field or display?

 

[bespokeappstt]

Can you step through the process for a calc element? I am not familiar with this.

 

[troester]

http://fabrikar.com/forums/index.php?wiki/calculation-element/
Add a calculation element
put the code in "More"
display the calc element in list view (remove id from list view)

 

[bespokeappstt]

One of the things that stumped me is that I don't see any option for a calculation element. Is this a recent update?

 

[troester]

It's not in Fabrik core, you have to install it.
If you are running the original Fabrik3.4.2 version (from download or Joomla update) get the calc element from the download area and install.
If you are running a GitHub update "discover" it in Extensions/Manage/Discover, select it and "Install".
Maybe additionally it has to be enabled (Extensions/Plugins)

 

[bespokeappstt]

How do I know the value of "yourtable" in return sprintf('%06d', (int)'{yourtable___id}');? The name if the internal id I want to perform the calculation on has the element name "advertise_your_trade_or_ser___id". Do I go into Mysql? How do i get the table name?

 

[troester]

The placeholder {...} needs exactly the full element name (which is table_name___short-element-name, with 3 underscores), you can copy/paste is from your element list.
So yes in your case
advertise_your_trade_or_ser___id

 

[bespokeappstt]

I used the element name and it appears to be working. Is it okay what i did? Don't want to continue doing the wrong thing even if it brings results.

 

[bespokeappstt]

Looks like we cross posted. Okay it's the right thing. Thanks.

 

[bespokeappstt]

And if I want to add it as a search field on my search form. I set it to field and left the defaults. But the search doesn't work. In the search form I kept the same name. How do I get it to work? I've noticed that when I search the figure 000012 in the filter it says I am searching 000000

 

[cheesegrits]

Ah, you didn't say you needed to search on it.

The problem there is that it's a chicken and egg issue. When you save a new form, the auto-inc PK doesn't exist, until after the form's data has been saved to the database. So on first save, your calc won't have a value. It'll display in lists, but only if you have "Calc on save only" set to No, which tells us to re-calc the value when rendering it.

So the data simply isn't in the table to be searched, until a given row gets edited and saved again, at which point the calc actually has the id value to work with.

One way to address this is instead of using a calc, use a simple field, and write a little form submission plugin that runs onAfterProcess, and manually updates the table with your serial.

$myId = (int) $formModel->formDataWithTableName['advertise_your_trade_or_ser___id'];
$mySerial = sprintf('%06d', $myId);
$myDb = FabrikWorker::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->update('advertise_your_trade_or_ser')->set('serial = ' . $myDb->quote($mySerial))->where('id = ' . $myDb->quote($myId));
$myDb->setQuery($myQuery);
$myDb->execute();

Change 'serial' to the (short, no tablename___ prefix) name of your serial field.

-- hugh

 

[bespokeappstt]

I'll put this solution on ice for the while. I deleted all the records and started over. Now the search is working just fine. I think using the calc only when saving is working just fine now that I am starting afresh. Won't implement that code solution as it's working. Having said that, what is your opinion on the matter?

 

[bespokeappstt]

Okay I am now stumped. The calc on save works quite well for the form Advertised Your Trade of Service but when I applied the exact same thing to the form Advertise To Be Hired the calc ID is not working at all. I only get zeroes. I have upgraded mysites credentials. Hoping I can get some help out here.

 

[cheesegrits]

I just checked, and it's doing exactly what I said it would do in my last post. Because the calc is based on something that doesn't exist when you first submit the form (the auto-incremented primary key), the calc won't work properly calculate and store to the database until the record is edited and re-saved.

If you look at your test record on that list, I edited and re-saved it, so now the sill id has it's value.

The only way to achieve what you need, and have a formatted ID field based on the PK value, created and searchable / sortable on first submission, is to use the form plugin approach I outlined in my last post.

-- hugh

 

[bespokeappstt]

Okay, I understand. Now to tackle your solution. The steps however are not quite clear. So I am going to:
delete the calc element - done
create a field element - done
form submission plugin that runs onAfterProcess(stuck here)

The onAfterProcess is that some kind of plugin I have to download? I need guidance on how to execute this process. Do I choose php from the dropdown in the form plugins as shown in the screenshot?

 

[bespokeappstt]

The part of your solution that says;

Change 'serial' to the (short, no tablename___ prefix) name of your serial field.

Does that mean I leave out the table name of the field I created? My new field has the full element name, 'advertise_your_trade_or_ser___my_skill_id' so I change 'serial' in the code to ___my_skill_id? So the code becomes

$myId=(int)$formModel->formDataWithTableName['advertise_your_trade_or_ser___id'];
$mySerial=sprintf('%06d',$myId);
$myDb= FabrikWorker::getDbo();
$myQuery=$myDb->getQuery(true);
$myQuery->update('advertise_your_trade_or_ser')->set('___my_skill_id= '.$myDb->quote($mySerial))->where('id = '.$myDb->quote($myId));
$myDb->setQuery($myQuery);
$myDb->execute();

 

[bespokeappstt]

Okay I have gotten the skill id to show with the zeros in front. I am now going to test the search feature and see if I can search it. Should I give the name of the element in the search form the same name as in the normal list form? So in the search form should I put the name to be advertise_your_trade_or_ser___my_skill_id?

 

[cheesegrits]

Just the "short" name, with no ___ prefix. That will get added automatically. So my_skill_id.

And in the PHP plugin, don't use the ___ prefix in the set().

Fabrik "full" element names are made up of the tablename and the field name, joined with ___. So an element called 'foo' on a table called 'mytable' will have a full name of mytable___foo. That's how we distinguish between element's with the same name on different tables.

So when creating database queries yourself, like in the code I gave you, you just use the plain table and short element names.

-- hugh