Sum Function for Data table

[AliciaDG]

Hi,

I have a data table that pulls data from a dropdown box and pulls information from a table.

What i am trying to do is figure out what the total number of Adults and kids where at a location.

Also i am stumped on how to have a date as a filter as well that is connected to the data table.

Thank you 

[AliciaDG]

Hi @WarrenBelz ,

Hoping you can help me with one final problem i am having with this app. i have tried for weeks to solve it and have given up. 

I am wanting to add a date dropdown or picker to the selections i already have. 

When i choose the date i want the gallery to only show me the items in that date? is that possible?

[WarrenBelz]

Hi @AliciaDG ,

Yes - you just need to filter the list, but dates have a bit of a different syntax and queries are also not Delegable - so

Filter(
   YourListName,
   Text(YourDateField,ShortDate)= Text(YourDatePickerName.SelectedDate,ShortDate)
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[AliciaDG]

Hi @WarrenBelz ,

Thanks for the quick reply, i did what you said. But not sure if i am putting the data in the right section. 

I put the formula in the on select of the date picker. it doesnt do anything. 

I have also tried adding it to the gallery2 on select but i always get the formula wrong. 

Currently this is what i have in my on select for gallery 2. Image below of what the app looks like

Search(
If(
//This is for all and all
'DDCampus Location'.Selected.Result = "All" And DDServiceLocation.Selected.Result = "-",
Church_Online,
//this is for all campus locations and selected services
'DDCampus Location'.Selected.Result = "All" And DDServiceLocation.Selected.Result <> "-",
Filter(
Church_Online,
'Please select the service you joined (QLD Time)?' = DDServiceLocation.Selected.Result
),
//This is for selected campus locations and all services
'DDCampus Location'.Selected.Result <> "All" And DDServiceLocation.Selected.Result = "-",
Filter(
Church_Online,
'Which Campus are you joining from?' = 'DDCampus Location'.Selected.Result
),
//This is for selected campus locations and selected services
'DDCampus Location'.Selected.Result <> "All" And DDServiceLocation.Selected.Result <> "-",
Filter(
Church_Online,
'Which Campus are you joining from?' = 'DDCampus Location'.Selected.Result And 'Please select the service you joined (QLD Time)?' = DDServiceLocation.Selected.Result
)
),
SearchBox.Text,
"Name:",
"Is this a creative gathering",
"Please select the service you joined (QLD Time)?"
)

[WarrenBelz]

Hi @AliciaDG ,

The formula I gave you is a filter for a gallery using the date in the date picker to match the date in the list. Is this what you are trying to do?

[AliciaDG]

Hi @WarrenBelz ,

yup that is what i am trying to do. However i am getting consfused as to where in the formula below i would add that formula. I have built this app watching multiple youtube videos i am still trying to figure out how each different formula can be coded per section.

[AliciaDG]

This is the formula i added

Filter(
Search(
If(
//This is for all and all
'DDCampus Location'.Selected.Result = "All" And DDServiceLocation.Selected.Result = "-",
Church_Online,
//this is for all campus locations and selected services
'DDCampus Location'.Selected.Result = "All" And DDServiceLocation.Selected.Result <> "-",
Filter(
Church_Online,
'Please select the service you joined (QLD Time)?' = DDServiceLocation.Selected.Result
),
//This is for selected campus locations and all services
'DDCampus Location'.Selected.Result <> "All" And DDServiceLocation.Selected.Result = "-",
Filter(
Church_Online,
'Which Campus are you joining from?' = 'DDCampus Location'.Selected.Result
),
//This is for selected campus locations and selected services
'DDCampus Location'.Selected.Result <> "All" And DDServiceLocation.Selected.Result <> "-",
Filter(
Church_Online,
'Which Campus are you joining from?' = 'DDCampus Location'.Selected.Result And 'Please select the service you joined (QLD Time)?' = DDServiceLocation.Selected.Result
)
),
SearchBox.Text,
"Name:",
"Is this a creative gathering",
"Please select the service you joined (QLD Time)?"
),
Church_Online,
Text('Date of Service Joined:',ShortDate)= Text(DatePicker4.SelectedDate,ShortDate)
)


Below is the issue i get

[WarrenBelz]

Hi @AliciaDG ,

You were missing some brackets etc - try this although I free-typed it and cannot test

SortByColumns(
   Search(
      Filter(
         Church_Online,
	     Text('Date of Service Joined:',ShortDate)= Text(DatePicker4.SelectedDate,ShortDate) &&
         If(
            'DDCampus Location'.Selected.Result = "All" && DDServiceLocation.Selected.Result <> "-",
 1          'Please select the service you joined (QLD Time)?' = DDServiceLocation.Selected.Result,
            'DDCampus Location'.Selected.Result <> "All" && DDServiceLocation.Selected.Result = "-",
            'Which Campus are you joining from?' = 'DDCampus Location'.Selected.Result,
            'DDCampus Location'.Selected.Result <> "All" && DDServiceLocation.Selected.Result <> "-",
            'Which Campus are you joining from?' = 'DDCampus Location'.Selected.Result && 
			'Please select the service you joined (QLD Time)?' = DDServiceLocation.Selected.Result
         )
      ),
      SearchBox.Text
   ),
   "Name:",
   "Is this a creative gathering",
   "Please select the service you joined (QLD Time)?"
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[AliciaDG]

thanks @WarrenBelz ,

the formula doesnt seem to work it comes up with 17 different formula errors. i have tried on and off today to fix it but gotten to a point of asking for assistance again. 🙂 

[WarrenBelz]

Hi @AliciaDG ,

All the errors I can see say that the column names you gave me do not exist. Go to your SharePoint list, hover over the field name and look at the bottom at the end of the string to see the actual field name.

[AliciaDG]

Hi again,

i started again. I have moved my data source to now be on sharepoint. but i am still running into the same problem the sort by columns function for the date picker i just can't seem to get it working. 

my current selection for items on gallery is

Search(

    If(

//This is for all and all   

        DDCampusLocation.Selected.Result = "All" And DDServiceLocation.Selected.Result = "-",

        Church_Online,

//this is for all campus locations and selected services

        'DDCampusLocation'.Selected.Result = "All" And DDServiceLocation.Selected.Result <> "-",

        Filter(

            Church_Online,

            'Please select the service you joined (QLD Time)?' = DDServiceLocation.Selected.Result

        ),

//This is for selected campus locations and all services

        'DDCampusLocation'.Selected.Result <> "All" And DDServiceLocation.Selected.Result = "-",

        Filter(

            Church_Online,

            Title = DDCampusLocation.Selected.Result

        ),

//This is for selected campus locations and selected services

        'DDCampusLocation'.Selected.Result <> "All" And DDServiceLocation.Selected.Result <> "-",

        Filter(

            Church_Online,

            Title = DDCampusLocation.Selected.Result And 'Please select the service you joined (QLD Time)?' = DDServiceLocation.Selected.Result

        )

    ),

    TextSearchBox1.Text,

    "Name_x003a_",

    "Is_x0020_this_x0020_a_x0020_crea",

    "Please_x0020_select_x0020_the_x0"

)