Notes Element - Additional Field?

[terryzzz]

I require to be able to date/time log the change of my Client Status which is selected by a dropdown. In the future I am hoping to create a report using the date difference between the Client Status changes.

I have the notes element working nicely in my client details section where as expected it logs the current user and date info against the updated notes record.

Is it possible to have the change of the Client Status recorded into the notes log section with the current user and date/time or will this need to be a separate function?

 

[cheesegrits]

Are you saying you want to automatically add a note when the Client Status is changed?

-- hugh

 

[terryzzz]

Yes. Automatically logging the user and date/time.

 

[cheesegrits]

It would be relatively easy to do it through Javascript when the status menu is changed, although that could result in spurious notes being added, if the form was then not submitted.

But to do that, you would put a JS 'change' event on your status dropdown element, which did something like this:

var status = Fabrik.getBlock('form_17').formElements.get('yourtable___status').getValue();
Fabrik.getBlock('form_17').formElements.get('yourtable___notes').field.value = "status changed to " + status;
Fabrik.getBlock('form_17').formElements.get('yourtable___notes').submit(new Event.Mock('click'));

Obviously change the form and element names to suit.

You could also do it manually with a PHP form submission, in which you would have to compare the original data for the form with the submitted data, and execute the appropriate query to enter a row in to your notes table if the value has changed. This would be more work, but would avoid any spurious notes.

-- hugh

 

[terryzzz]

Wow... thank you. There would have been no way I would have managed to get that going with my extremely limited JS knowledge. It is now working... and very nicely. Thank you. I think I need to really knuckle down and learn some more JS and PHP, but all the other things I do get in the way time wise .

As I mentioned above, I would like to use these date and time 'logs' to action a 'time between status change' report. Will I be able to do this? Is there a way to register the difference between a Status change and a Notes update with the above ?

In my head I see the need for an integer to be applied to a Status option, so I can count the number of Status's that are currently set to 'Assigned' or 'Completed' etc. Would this need to be a separate element that I can update on Status change, or can fabrik count the number of status's set to a current option already?

I'm reading back over what I've written, and I see you looking at this and thinking in your head that I'm one of those mechanical monkeys banging his cymbals together lol. So I'll draw a pretty picture cos thats what I can actually do...to help explain it all

 

[terryzzz]

 

[rob]

As I mentioned above, I would like to use these date and time 'logs' to action a 'time between status change' report. Will I be able to do this?

I have a feeling that what Hugh suggested might be too simplistic for what you are describing here.

I think you are going to need to create a new list, 'status_updates' with the relevant information you want to store regarding a status change.

Then when you primary form is submitted, use a form PHP plugin to insert data into the status_updates database table. That way you will have a complete history of the status changes, and not just the last status change that occurred. Then from that data you can work out times between various status changes etc.

Theres an example of how to use the form PHP plugin to do this here/:
http://fabrikar.com/forums/index.ph...e-a-form-s-field-with-the-records-primary-key
(you can ignore the last line about updating the main table with the insert id as I don't think that is necessary here)

 

[terryzzz]

Okay.... thanks for taking the time to look into this.

Unfortunately this is where my skill level drops off. Just so you know, my current Status element is a dropdown.

From what I understand you are saying:

1) Create StatusUpdates table with field elements for 'status', 'changedate' and 'userid'?
2) Use following php script on initial data input form via PHP plugin. Set to End Of Form Submission?

$db= JFactory::getDbo();
$query=$db->getQuery(true);
$query->insert('tablename')->set('field = '.$db->quote('bar'))
->set('field2 = '.$db->quote('{tablename___elementname}');
$db->setQuery($query);
$db->execute();
$id=$db->insertid();

3) My php knowledge is so limited its pointless, so although I kinda get the reasoning behind the above, I'm struggling with which table and elements I put where? Also I don't understand the 'bar' quote?
4) I figure this is how its supposed to operate.
In my main form, I change the status dropdown to an option and then save/submit.
This script then is supposed to complete the 3 fields in my new StatusUpdates table??? Do I keep the current operation of the JS event that Hugh put me on to? I guess it doesn't make any difference if it displays which is the way I want the details to look anyway.
This php script creates the same data (that Hughes JS event displays) stored in a separate table with separate fields to make it easier to use?

Sorry I'm so uneducated on all of this. But I guess I'm learning with every step! lol

Cheers for your patience

 

[terryzzz]

It would be relatively easy to do it through Javascript when the status menu is changed, although that could result in spurious notes being added, if the form was then not submitted.

But to do that, you would put a JS 'change' event on your status dropdown element, which did something like this:

var status = Fabrik.getBlock('form_17').formElements.get('yourtable___status').getValue();
Fabrik.getBlock('form_17').formElements.get('yourtable___notes').field.value = "status changed to " + status;
Fabrik.getBlock('form_17').formElements.get('yourtable___notes').submit(new Event.Mock('click'));

Obviously change the form and element names to suit.

You could also do it manually with a PHP form submission, in which you would have to compare the original data for the form with the submitted data, and execute the appropriate query to enter a row in to your notes table if the value has changed. This would be more work, but would avoid any spurious notes.

-- hugh

Hi Hugh,

In the following piece of your code "var status = Fabrik.getBlock('form_17').formElements.get('yourtable___status').getValue();" instead of get the value of the dropdown option, can I get the Label text? tried a few things but nothing is working :/ Cheers

 

[cheesegrits]

With Rob's solution, you don't need the JS. You just set the status in the form submission script.

The code Rob gave you is just a generic example, so the 'bar' quote is just where you might want to put some static text, like "Status update". You could also set a user element in the status table by doing set('userid = ' . $this->user->get('id')).

-- hugh

 

[terryzzz]

I'm sorry to say it but I'm really struggling with this:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->insert('tablename')->set('field = ' . $db->quote('bar'))
->set('field2 = ' . $db->quote('{tablename___elementname}');
$db->setQuery($query);
$db->execute();
$id = $db->insertid();

I can't get it to do anything :/. I've tried many combinations but it doesn't instruct on which table when it says 'tablename' (the form table or the table you're trying to populate/update) and which elements in which table.

I have two tables for this now:

Table 1: client___
Elements: client___status

Table 2: statusupdates___
Elements: statusupdates___id (internalid)
statusupdates___username (field)
statusupdates___status (field)
statusupdates___changedate (field)

I'm placing this code into the PHP plugin on my 'client___' form setting it to End of form submission (onAfterProcess).

The idea as I understand it is:
on updating the client___status this code will populate the statusupdates___ columns? I need this to work so I can have users, status and dates to create a report or time between the changes and the number of statuses of a particular kind.

As you can tell I'm rather lost. Sorry

 

[terryzzz]

am I also correct in thinking that:
->set('field2 = ' . $db->quote('{tablename___elementname}');
should have another ')' at the end?

 

[cheesegrits]

Yes.

I'm looking at your form code now.

-- hugh

 

[cheesegrits]

OK, before we do anything else, I would strongly suggest that you turn your 'status' element into a join to a 'status' table, using numeric values, rather than a dropdown element with string values.

So create a 'status' list, and just add a 'status_name' (or whatever) to it. Add all your current statuses (statii?) to it by adding new rows to the list (best to add them in the order you want them displayed on the status dropdown menu). Then turn your 'status' elements (on the client forms and the status update form) into joins which us the 'id' as the value and the 'status_name' as the label.

This may seem like more work in the short term, but in the long term is much more sustainable.

I would do it for you, but I didn't want to just go in and start making wholesale changes without discussing it with you.

-- hugh

 

[terryzzz]

Firstly, thank you very much for looking into this and for your advice. I think the method I see you heading down is definitely on a tact that I kind of had figured in my head but had no concept of how to go about it.

Secondly, thank you for not making the changes yourself. I'm sure it would be quicker if you did, but I learn hands on and just completing the steps above has taught me a number of things I didn't know. I've not performed a database join before so that was good!
It also took me a little bit to figure out that adding the current 'statii' (love that so called the list that lol) was by adding 'data' to the list field and not adding list elements. Another first for me! But its all a learning experience. (here's hoping thats what you meant but its the only way I could get it to populate the dropdown with the statii)

So that section is completed and running. I can't seem to order the statii as it looks like its defaulting to alphabetical even though I added them in the order I want. Not a huge issue but if I've missed something to allow me to order I'd appreciate the advice.
I'm also hoping to keep the notes update section working with the Status change. At the moment it still works but gives the corresponding number of the Statii field data and not the label. However I'm sure this is all in your plan of things to come so I wait for your next instruction with baited breath

P.S.... thank you again

 

[cheesegrits]

Ah, yeah, you'll need to order it in the WHERE clause (Data Where tab). Like ...

ORDER BY {thistable}.id

-- hugh

 

[terryzzz]

Okay.... so thats all done. So two things I have noticed.

1) The Status selection dropdown does not hold the selected Status and reverts the first in the list. In the Details Display I have, this was the method I was using to show where the clients current Status was at.

2) In the 'notes' section where I am displaying the Status change with User and Date, It displays the Status field number and not the label.

I'm probably jumping well ahead of myself here as no doubt your plan includes a way for this all to work together, so sorry if thats the case. Just thought I'd mention my observations.

 

[terryzzz]

Okay by playing with the Database Join parameters, by changing the 'Data' tab where I've selected the table 'Statii' I've changed the Value and Label to 'statusname' and that now displays the correct text in the 'Notes' section of the Details Display. Whether or not this affects the purpose you have in mind is to be discovered. Cheers

 

[terryzzz]

Righto... more playing and everything in the previous 2 items is now fixed. The Status holds if the changed is saved. Previously if it wasn't saved it would still hold

 

[terryzzz]

Well I fixed something... which was a little exciting as I'm obviously learning as I go. When I had the statuses in a dropdown element (not database joined) I could set one of the dropdown options to default. I wasn't able to do this with the new setup, so I used php to force the Status on initial Submit.

$formModel->updateFormData('client___status', 'Not Assigned', true);

Works perfectly! Please excuse my childish glee but its nice to be able to solve little issues.

Any further thoughts on how to go about using your new statii list to help produce reports?

Cheers