Forums

Hi
​
I have a repeater area with a data source which displays all records from a table. Is it possible to make the repeater area display DISTINCT values for a field? I can't see how to do this in the read data object or the repeater.
​
So, currently it shows all bookings that do not clash, but I just want to see records with a distinct asset_id
​
Thanks
​
Mikki

Hi Mikki,
​
I think that you should be able to specify DISTINCT as part of your query in the Conditions box of the Read Data action; if not, you could use PHP to filter the results - not ideal but should work.
​
Bob

You can in fact use DISTINCT, in Fields to Retrieve.
​
e.g.

DISTINCT(user.id):user.id

​
Note that the brackets and the :model.field at the end are necessary.
​
You can combine these functions to make more complex requirements, just make sure you pay attention to the spaces between sections, they are also required.
​
e.g.

COUNT( DISTINCT if(job.client_id is null , '' , job.client_id) , if(job.client_name is null , '' , job.client_name)):countof.clients

Thanks that's great
​
So I am trying to create an SQL query that finds the booked assets and takes them away from the returned results.
​
Can I build this entire SQL string in the Fields to retrieve box?
​
The relationship between the tables is defined in the model ids chosen, so I tried putting this in the Fields box, but I just get errors.
​
DISTINCT booking.asset_id MINUS SELECT booking.asset_id WHERE booking.start_date <={data:end_date} AND booking.end_date >={data:start:date}
​
Is there a way to do this?
​
Thanks
​
Mikki

Why not just have in the where conditions box
​

booking.start_date/>:{data:end_date}
booking.end_date/<:{data:start_date} 

​
???
​
I mean it depends on your table set up and business logic. How is it determined that an asset is booked?

Hi
​
My setup is a user selects an asset type and then a start and end date. I then need to display which assets are available, so they can choose one.
​
I tried
​
​

booking.start_date/>:{data:end_date}
booking.end_date/<:{data:start_date} 

​
and it displays all of the booked assets in the booking table that do not clash with the requested dates, but not assets in bookings that clash, so I can't know that the assets in the displayed bookings are free on the requested date.
​
So if OhBot1 is booked out in March and May, and the user tries to book during May, the March booking will display the asset even though it is booked out.
​
I think I need to find all of the bookings that do occur on the requested dates, and then take those assets out of the list of booked assets returned, with a MINUS or NOT IN.
​
But that needs 2 SELECTS and I don't know if I can run that all in one date read.
​
Otherwise I guess I can try to do it in PHP...
​
Mikki

Just trying to work it out.
​
What would an asset looked like that's NOT booked out on the current date?
​
Also: You could just do two data reads. One that retrieves all the booked assets as you said, and the second one try this in the where conditions
​

booking.asset_id/not in:{var:read_data#.[n].booking.asset_id}

replace # with the correct number of your first read data action.

Wow, that works, but if threre are no bookings found in the first read data action, because there are no clashes on the date chosen, I get an SQL error from the second read_data action.

put a /- at the end of the condition.
​
​

booking.asset_id/not in:{var:read_data#.[n].booking.asset_id}/-

​
Either that or put the second read data in the "Found" event of the first

Hi
​
The data reads all appear to work well but I am having trouble saving the form data.
​
To troubleshoot this I have simplified the form to display just the first asset returned, and set the second data read to return only one record.
​
The fields on the from display data correctly, and asset_id is in the array before I hit the submit button:
​
​

 [var] => Array
                (
                    [asset] => Array
                        (
                            [dropcrate_id] => Spheros1
                            [user_id] => 0
                            [created] => 2018-08-17 17:11:30
                            [asset_owner] => Cleeve
                            [type_of_resource] => Spheros
                            [description] => A dropcrate of 12 Spheros
                            [state] => 1
                            [aid] => 0
                            [asset_id] => 69
                        )

                    [booking] => Array
                        (
                            [id] => 60
                            [user_id] => 225
                            [created] => 2018-08-17 17:11:30
                            [request_delivery] => 
                            [location] => 
                            [aid] => 0
                            [asset_id] => 69
                        )

                )

​
​
When I click on submit, asset_id is in the form data, but not in the save data array:

Array
(
    [option] => com_chronoforms6
    [cont] => manager
    [chronoform] => new-booking-v4
    [event] => submit
    [asset_id] => 69
    [description] => A dropcrate of 12 Spheros
    [Location_location] => 
    [start_date] => 2018-08-20
    [end_date] => 2018-08-22
    [request_delivery] => 
    [button78] => 
)

Array
(
    [validate_fields2] => Array
        (
            [log] => Automatic validation enabled.
            [var] => 1
        )

    [save_data12] => Array
        (
            [data] => Array
                (
                    [created] => 2018-08-20 08:49:07
                    [user_id] => 225
                    [description] => A dropcrate of 12 Spheros
                    [location] => 
                    [start_date] => 2018-08-20
                    [end_date] => 2018-08-22
                    [dropcrate_id] => 
                    [asset_id] => 
                    [request_delivery] => 
                    [previous_location] => 
                    [available_from] => 
                    [dropbox_id] => 
                )

            [_success] => Data saved successfully
            [log] => Array
                (
                    [0] => INSERT INTO `k5jm3_tblbookings` (`created`, `user_id`, `location`, `start_date`, `end_date`, `asset_id`, `request_delivery`)  values  ('2018-08-20 08:49:07', '225', '', '2018-08-20', '2018-08-22', '', '');
                )

            [var] => Array
                (
                    [created] => 2018-08-20 08:49:07
                    [user_id] => 225
                    [location] => 
                    [start_date] => 2018-08-20
                    [end_date] => 2018-08-22
                    [asset_id] => 
                    [request_delivery] => 
                    [id] => 129
                )

        )

)

Post a screenshot of your save data action . Also you won't be able to save your data correctly with your fields named that way if you get more than one in your repeater area.

Hi
​
I removed repeater area to simplify the form and solve why it wasn't saving.
​
Here is the save action:
​
I also tried it with the action set to INSERT but still not working:
​

ok so.... your save data action is basically empty? OF COURSE it's not saving it to the database because you're not telling it to.
​
Might be time to have a read of the instructions.

Just put in manually the things you want saved, don't rely on the auto add fields setting.

Thanks - I have checked through the manual again.
​
I didn't specify a data provider because I was using 'Auto save fields' and as the asset_id was in the form data array I just expected it to go in like all the other fields were going in.
​
I have disabled auto save fields and explicitly specified all of the fields in the 'Data override on Insert' option and now it is all saving.
​
Thanks again for all of you help on this
​
Mikki

No problem. Now you need to have that save data action in a loop event, and you'll need to rename all your fields in your repeater to handle multiple items. Probably for example the name of the asset_id field should be asset[{var: repeater_area#.key}][asset_id]

Hi
Is there a way to have a checkbox by each item in the repeater so if two boxes are checked, they are saved in 2 separate entries to the data table?
​
Thanks
​
Mikki

Thanks for the help - I will try it