How to increment a calculated field in a repeatable group?

[RobertG]

Hi,

In a repeatable group, I need to add a different value to each registered row.
I have an arrival date and a departure date. If there is 2 or more weeks, the group is repeated.
The columns are id_stay, id_option, id_activity and id_week.
id_stay is the parent_id, option and activity come from dropdown lists.

The first value of id_week is calculated by a calc element from the arrival date, and for the next row, I need to add "1" to that value, etc.. By default only the calculated value is added to all rows.

How can I do ?
Thanks!

 

[troester]

This may help
https://fabrikar.com/forums/index.p...lement/#retrieving-data-from-complex-elements

 

[RobertG]

Thanks troester but I don't think it helps.

When a stay is booked, it lasts one or more weeks, from sunday to saturday (users can only select saturday and sunday).
For each week, one can select (or not) one option and one activity.

I calculate (in a cal element) the id_week value looking in a "week" table for the date of the first sunday of stay.

For example, if the arrival date is 2023-07-16, the id_week will be 709 and Fabrik uses it for all the repeat rows.
If the stays lasts 2 weeks, the second one begins on 2023-07-23 and the id_week will be 710, not 709. How to add 1, 2 and so on for the second, third... weeks rows?

 

[juuser]

I think you need to explain your workflow in more detail. And keeping in mind what the users should do (what's the goal), not what they do should do according to your current set-up.

In that way there's a lot more chance to get the answer, because there could be another better method to achieve what you want.

For me it's still unclear what is the id you are chasing and what are you doing with it. What is the "week" table and what data does that exactly contain etc.

 

[RobertG]

I explained it.
In the repeatable group, I have two dropdown lists (from options and activities tables). I need to add the ID for each week, that is in another table (I compare the arrival date with the second column to get the id, the first colum value:

('709','2023-07-16','2023-07-22','2023'),
('710','2023-07-23','2023-07-29','2023'),

Here I selected two weeks for a stay from 2023-07-16 to 2023-07-29.
The arrival date is 2023-07-16 so the week id is 709. The second week begins on 2023-07-23 so its id is 710: how to obtain 710 to store it in the second row of the repeat table?

 

[juuser]

What is your code for the "ID week" calc element?

Are "ajax calculation" and "calc on repeat" selected?

Why is your parent_id element not hidden? This element's value should not be manually changed.

 

[RobertG]

The parent_id and id week are displayed for testing
The code for id week is

date_default_timezone_set('UTC');

$date='{hr5i3_fb_stay___arrival_date}';
$wd_date = date("w",strtotime($date));

if ($wd_date == 6) {
  $my_Date = date('Y-m-d',strtotime($date . "+ 1 day"));
} else {
  $my_Date = date('Y-m-d', strtotime($date));
}

$myDb = Joomla\CMS\Factory::getContainer()->get('DatabaseDriver');
$query = $myDb
    ->getQuery(true)
    ->select ('id')
    ->from($myDb->quoteName('hr5i3_week'))
    ->where ($myDb->quoteName('date_starts') . " = " . $myDb->quote($my_Date));

$myDb->setQuery($query);
$id_week = $myDb->loadResult();
return $id_week;

Calc on load and on repeat are activated. Ajax calculation observe hr5i3_fb_stay___arrival_date

 

[juuser]

In your "where" clause you are comparing "date_starts" which is in format "Y-m-d hh:mm:ss" with a $my_Date which is in format "Y-m-d", so this will not work. Try:
->where ($myDb->quoteName('DATE(date_starts)') . " = " . $myDb->quote($my_Date));

 

[RobertG]

As you can see above (#4) and below, date_starts is in format "Y-m-d"

('709','2023-07-16','2023-07-22','2023'),
('710','2023-07-23','2023-07-29','2023'),

So I compare two dates in the same format.
If I use ('date(date_starts)') I get th error "Unknown column 'DATE(date_starts)' in 'where clause'"
If I use (date('date_starts'))I get the error "Unknown column '11am31UTC_3031amTue, 11 Jul 2023 10?? +00003130' in 'where clause'"

 

[juuser]

Okay, as I do not see your database and field types, I assumed your date element's data type is likely "datetime". But it seems that is not the case.

I think you just need to start debugging.

1) First, return your "$my_Date" variable right before your query starts in the code and see if it displays correct date for both repeat rows.
2) Then, if the records are correct in previous point, then test your query in PhpMyAdmin with the hardcoded dates from previous points, e.g. like:
SELECT id FROM hr5i3_week WHERE date_starts = '2023-07-23'
and see if that gives expected result etc.

 

[RobertG]

The above code finds the row and returns 709 for the first week, so it's correct, but no calculation for the second week and the week 710.

If I return $my_Date, it's the same on the two rows, instead of 2023-07-16 and 2023-07-23
The code doesn't add a week to the first date, for the second row.

 

[juuser]

I really don't understand your setup as I cannot see it.

I thought that user selects the date in the repeat group and then in calc element you fetch the corresponding id for the selected date from week table. But in your screenshot there is nothing selected by the user.

 

[RobertG]

Users select the arrival and departure dates in a first group. The second one is a repeatable group where the "arrival date" can be used for the first week.
I can calculate the week id for that first row, not for the following rows.

 

[juuser]

I'm still quite lost regarding your set-up and your actual goal. Unfortunately this information is just not sufficient (at least for me).

If the user selects arrival date in another group, how would you expect to get different id-s for the same date in repeat group? What element type is the "Arrival date". etc.

As you don't have any element selected in your repeat group and expect different result for each calc on different repeat rows, I think it's not quite possible. I have seen threads discussing about getting repeat number in calc element with PHP using {i} or "input" data, but I have never seen this actually working. Probably because repeat rows are added in front-end "on the fly", so it's not possible to retrieve this data from back-end with PHP.

You can get the repeat row number easily in javascript (e.g. in form_x.js) and then it's possible to use function in user_ajax.php to retrieve your values and pass to corresponding row/field in repeat group.

 

[RobertG]

When an user select arrival and departure dates (date elements) on the first group, the second create as much repeated rows as nb of weeks.
Arrival date can only been selected in the first group, nowhere else.
I can use the selected arrival date to found the week number in the week table of the database.
I was wondering whether it would be possible to create a table with the value of this id for row 0 and an id calculated from it for each subsequent row.
In the example, I calculate 709 and use it for $id_week[O], then I calculate 709+1 for $id_week[1], the return $id_week: the repeatable group would it be able to add the values form this table to each row?

I'm not really comfortable with JS.

 

[RobertG]

Here you can see the two groups

 

[juuser]

Okay I think it's a bit more clear now. Like you probably already realized yourself, there are no "out of the box" solutions for this. So it needs some fair amount of customization and coding. I would approach it like this:

1) User starts creating a new record and fills in the fields on the "Stay" tab. "Stay options and activities" and "Total Price" tab/group are hidden by js (condition when rowid is empty).

2) After user has filled the "Stay" tab, he/she will press "Save" button on the form which you can rename to "Continue" or "Confirm data" for example.

3) Now on form submit, you create your repeat table rows with a PHP code in form php-plugin, run on New and onAfterProcess. And redirect to the newly created form record as the last part of your code. You can get your week id here according to the dates entered by the user using similar code what you have above. You need to take the arrival and departure date, take all the week start dates that fall between these dates, look up the week id and insert it to the repeat table among with the other data.

4) So, now you have the "Stay options and activities" tab visible with the newly created repeat rows as the hiding in the clause 1 was done only for a new record. In the repeat group you probably would want to set the rights for adding and deleting row to a access level "No-one" so the user cannot add or remove rows. If you do not have the "No-One", just create new Joomla access level and do not assign any user groups to it.
Also, you would want to set the date elements access settings on the "Stay" tab like "Form (edit)" to "No-one", so the user could not make any changes for the dates which may cause the need for updating the repeat row amount.

5) Now the user fills in the repeat group and can click on form's save (Continue) button again. User is now redirected normally to the list or you can have another redirection as form plugin (run on Edit) to redirect anywhere you like. And you can also add a custom Joomla system message after redirect, something like "Booking successfully confirmed...".

As you do not have too many elements on your form, I recommend keeping them all on one page for clarity (use normal bootstrap) template. So e.g. the user would not have to click on "Total" tab just to see the cost.

This is probably not the one and only way to do it, just my ideas as a long time Fabrik user. And of course the whole workflow has not been tested, so it may need some adjustments on the go.

 

[RobertG]

Thank you very much!

Your approach is interesting, but the data on the first three tabs should not be saved if the visitor wants only a quote.
It's only an "information" quote that should only become a booking if the visitor decides to place an order by clicking on a radio button in the "total price" group, which brings up the "student details" group and enables validation.

We do not want a record to be made every time a visitor estimates the cost of a stay, but only if a reservation is made.

In addition, there is no registration of the user as a member of the Joomla! site when he books a stay, only the content of the form is registered in the main stays table and the "repeat" table.

 

[juuser]

I still don't see any other good and reliable options.

You could add the "confirmed" element to your table and set it to "1" if the user confirms the booking. And then e.g. in your list you can set a pre-filter to only show confirmed rows.

In addition you could set up a list php events plugin or cron job to delete the not confirmed rows which are e.g. older than a few days.

But you can also consider that "abandoned" quotations can be a valuable information to analyze.