Hi all,
I want to ask you if is it possible to remove some rows automatically when a condition is valid. I have a column in my table where the time is displayed like this (HH:MM: 16:30, 18:45). I would like to compare this with windows actual time and after one hour let’s say to be automatically deleted. If windows time is 17:29 when it will be 17:30 it will automatically delete the row that has in the respectively column 16:30.
I would appreciate any answer. Thanks!
hmm if you mean like literally delete the row form the dataset then as far as I know, no.
It won’t be displayed with correct filter, but won’t be deleted (that you’d need to do manually)
Or at least to put a condition to not be displayed any more?
I found a script to do this, or partially because it is not deleting the row but it is hiding it. The script is this:
$(‘tr:has(td:contains(“text”))’).hide()
When finds the “text” it hides the row that it contains it. If someone wants to use it, modify the modules\preview\HtmlTemplate.html and add this code at the javascript content.
My problem is that I want this to happen after a time of 30 seconds let’s say, the soft detects that the specific text was entered by the user in dataset, it waits 30 seconds and after that it hides the row. Another problem is that the table hides that row and instead of it will be the black screen that is specific of layout. I would like the table to remain at its size and all his properties, only the content from that row to be removed. Does someone know if can I do this? Thank you!
If you can define your condition as a dataset filter then there’s no need to customise anything.
All you do is enter your filter condition
So for example something like
ColumnName <> ‘text’
That will then only show rows in which ColumName doesn’t equal text. You could use like instead if you want a substring match.
The ideea is that the “text” will not be in the dataset from the beginning. It will be entered manually by a user at a moment. The question is if that filter condition query the database regularly to see if something is changed in the content of cells. If it does, can I add a simple sql query syntax to remove the content of that row after 30 seconds when it detects that “text”?
I found a script that it goes partially, here it is:
$(‘tr:has(td:contains(“text”))’).delay(10000).hide(0)
The code goes fine, after 10 seconds it hides the row that I want, but the problem is that when client refresh the information that row appears again and after 10 seconds it hides. Do you have any ideea how can I resolve this? Could I remove the content of that row directly in database? Many thanks.
You’d need a column in the dataset that held the time the text was added. You can then define a filter that ignores rows that are older than 30 seconds ago.
That should work, but you’re constrained by the player collection interval - so you won’t be able to refresh much faster than every minute or so that way.
It’s ok, I can choose to ignore that row after 90 seconds and select the collection interval: 90 seconds. I understand that the admin has to manually introduce the time when he enter that “text”? Or the column that will held the time can be automatically updated with the time when admin only entered that text in database?
You can set it as a formula type and use NOW() function to enter the current date and time automatically (I think) although test that as it may be re-evaluated each time the dataset is read.
Otherwise yes you would need to have that entered manually.
I searched on google but I didn’t find something that can help me. From what I understand I need a script that I’ll put it on formula type of the column that has to enter the current date (NOW()) but to do this only when it detects that “text” from that column that is on the same row with it.
Can you give me an ideea or where do I have to look at?
You don’t need any script. You create a column called say TimeStamp and set it to type DATETIME and then make it a FORMULA and then enter the formula NOW()
That means every row will have a timestamp on it. Then you define a filter on the dataset view that checks for Text in the row and if the timestamp is within whatever range you want.
Ok, so anytime admin modifies something on that row, the value of the column “TimeStamp” will be updated. Is it possible to write a script that will detect if a “text” exists on that row and to hide/remove elements of that row after a period, let’s say something like the value from “TimeStamp” + 90 seconds?
I really can’t explain it any other way than I have already.
No script is required. Nothing clever. You simply put a filter on your dataset view, that says that it shows only items where the timestamp is less than 90 seconds old, and the text doesn’t contain “text”. Then only the rows you want will be shown. When the timestamp is more than 90 seconds old, then next layout refresh the rows will go away automatically.
I put that column with formula NOW() and it is modifying everytime when it makes a refresh and it is the same for all rows. I really don’t know how to write exactly in filter from datasetview to display that row that contains “text” only for 90 seconds. I would be grateful if you can help me. Thank you so much!
I tried something like this but it doesnt’t work and the layout blocks:
ColumnName <> ‘Text’ where(TimeStamp + interval 90 second)>= now()
Can someone help me with a valid syntax? Sorry for asking stupid questions but I am a beginner in this. Thank you!
I replaced “where” with “AND” and no change, I saw in other examples posted here that worked with “AND”. Is it possible to do it in this way or to try something else?
If the formula value of NOW() changes for all rows when you update one then this isn’t going to work I’m afraid.
WRT the syntax, you can’t just make it up.
It’s MySQL syntax so yes it would be
ColumnName <> ‘Text’ AND (TimeStamp + INTERVAL 90 SECOND) > NOW()
Which means ColumnName isn’t exactly “Text” and the Timestamp is up to 90 seconds old. But if the Timestamp Column is always being update then it won’t work anyway. I don’t think Xibo can do what you want directly on that basis.
Maybe you’re right and I will think to do it in other way. But the problem at that syntax is that it blocks the layout. If I only write ColumnName <> ‘Text’ is ok and it hides the row that contains “text”, but if I add the rest of command it blocks. Even if I only write TimeStamp = NOW() or TimeStamp <> NOW() it still blocks. Do you know why? Thanks a lot!
I am trying something else. I want to write some php code to manually modify something in the database. I did a new function, I copied the function Edit from datasetdata.data.class.php and I modified it a little to do what I want to do. Here it is:
public function Editt($dataSetColumnIdd, $rowNumberr, $valuee)
{
$dataSetColumnIdd == 24;
$rowNumberr == 3;
$valuee == 'test';
if ($dataSetColumnIdd == 0 || $dataSetColumnIdd == '')
return $this->SetError(25001, __('Missing dataSetColumnIdd'));
if ($rowNumberr == 0 || $rowNumberr == '')
return $this->SetError(25001, __('Missing rowNumberr'));
try {
$dbh = PDOConnect::init();
$SQL = "UPDATE datasetdata SET Value = :valuee ";
$SQL .= " WHERE DataSetColumnID = :datasetcolumnidd AND RowNumber = :rownumberr";
$sth = $dbh->prepare($SQL);
$sth->execute(array(
'datasetcolumnid' => $dataSetColumnIdd,
'rownumber' => $rowNumberr,
'value' => $valuee
));
$this->UpdateWatermarkWithColumnId($dataSetColumnIdd);
Debug::LogEntry('audit', 'Complete', 'DataSetData', 'Edit');
return true;
}
catch (Exception $e) {
Debug::LogEntry('error', $e->getMessage());
return $this->SetError(25005, __('Could not edit DataSet Data'));
}
}
I don’t know if it is in the right place because I am pretty sure that the code should work. Can someone give me an ideea? Thank you!