cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Help with passing variables between queries & forms

Hi guys,

 

I have done some search on the blogs and can't seem to find an answer to my problem.

 

I have an app which has an inittial screen with a search and gallery (summaryGal) on it. The data source servicing this is a SQL view ([dbo].[vw_call_centre]). There is a PK attribute in this view called CentreCode.

 

I have a second screen with a display form on it. This display form will show the opening hours of a centre. The opening hours is stored in a new SQL table which i want to access & filter based on the previous selection in the summaryGal.

 

Any assistance on how to filter the display form with the PK attribute from the previous selection would be greatly appreciated.

 

Thanks 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Anonymous

 


Are you able to clarify what "replace 'ThisItem.Time' with the name of the value that contains the time column):" actually means? i assume "value" is incorrect and it needs the variable or data card?

I have 2 DC's and have tried updating the ThisItem.Time to the DC eg openFromDC.Time.


What this means is that it's necessary to replace ThisItem.Time with a reference to the field that contains your time data. So in the case of where you want to display OPEN_FROM, the formula you would use is this:

 

 Time(
        If(
            IsBlank(Find("H", ThisItem.OPEN_FROM)), // Check if there is an hour component
            0,                                 // If not, the value is zero
            Value(                             // Otherwise, take the substring between 'PT' and 'H'
                Mid(
                    ThisItem.OPEN_FROM,
                    3,
                    Find("H", ThisItem.OPEN_FROM) - 3))),
        If(
            IsBlank(Find("M", ThisItem.OPEN_FROM)), // Check if there is a minute component
            0,                                 // If not, the value is zero
            Value(                             // Otherwise take the substring between the rest of the value
                Mid(                           //     after the hour component and the 'M' indicator
                    ThisItem.OPEN_FROM,
                    If(
                        IsBlank(Find("H", ThisItem.OPEN_FROM)),
                        3,
                        Find("H", ThisItem.OPEN_FROM) + 1),
                    Find("M", ThisItem.OPEN_FROM) -
                        If(
                            IsBlank(Find("H", ThisItem.OPEN_FROM)),
                            3,
                            Find("H", ThisItem.OPEN_FROM) + 1)))),
        If(
            IsBlank(Find("S", ThisItem.OPEN_FROM)), // Check if there is a second component
            0,                                 // If not, the value is zero
            Value(                             // Otherwise take the substring after the minute or hour indicator,
                Substitute(                    //     remove the 'S' indicator, then take the value
                    Mid(
                        ThisItem.OPEN_FROM,
                        If(
                            !IsBlank(Find("M", ThisItem.OPEN_FROM)),
                                Find("M", ThisItem.OPEN_FROM) + 1,
                            !IsBlank(Find("H", ThisItem.OPEN_FROM)),
                                Find("H", ThisItem.OPEN_FROM) + 1,
                            3)),
                "S",
                ""))))

And in the case of OPEN_TO, you would replace ThisItem.OPEN_FROM with ThisItem.OPEN_TO.

 

 

 

 

View solution in original post

8 REPLIES 8
Drrickryp
Super User
Super User

Set the datasource property of the form to the call center table. Set the item property to Lookup(callcentertable, foreign keyID=summarygall.Selected.ID)
pardon the inaccuracies, I'm doing this on a cell phone but hopefully you will get the idea.
Anonymous
Not applicable

Thanks for the speedy response.

 

 

It looks like i have worked it out with your help. so Thank you.

 

LookUp('[dbo].[OPENING_HOURS]',CENTRE_CODE=summaryGal.Selected.CentreCode)

What i am now getting though is some kind of encoded time value from SQL.

 

Do you know how i can format this in time in the PowerApp??

 

 

v-xida-msft
Community Support
Community Support

Hi @Anonymous,

Could you please share a bit more about your scenario?

Do you want to filter your Display form based on the PK attribute (CentreCode) selected from summaryGal?

I have made a test on my side, please take a try with the following workaround:

Set the Item property of the Display form control to following:

LookUp('[dbo].[YourSQLTable]', ColumnName = summaryGal.Selected.CentreCode)

Note: The ColumnName represents the column in your SQL Table, which you want to compare with the CentreCode value selected from your summaryGal.

More details about the LookUp function, please check the following article:

LookUp function

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous

Can you confirm the data type of the OPEN_FROM and OPEN_TO fields in your [dbo].[OPENING_HOURS] table?

If the data types of these columns are 'time', it would explain the format that you're seeing.

@CarlosFigueira explains this behaviour in the following post, and also provides a formula that you can use to better format your time values.

https://powerusers.microsoft.com/t5/General-Discussion/issue-with-sql-time-format-with-azure-db/m-p/171021#M57179

 

Anonymous
Not applicable

Hi Timl

 

Yep they are a time data type.

 

I have just looked at that post and have copied in the DAX script however im getting a lot of red squiggly...

Are you able to clarify what "replace 'ThisItem.Time' with the name of the value that contains the time column):" actually means? i assume "value" is incorrect and it needs the variable or data card?

 

I have 2 DC's and have tried updating the ThisItem.Time to the DC eg openFromDC.Time. 

 

Still a lot of red squiggly...

 

Any help is greatly appreciated.

 

Thanks

Anonymous
Not applicable

You for your reply. I have been able t replicate your code and have got it working.

 

Thanks!

 

I am now having a problem displaying the time attribute from the SQL DB. Please see my below comment and screen capture if you are able to assist?

 

Thanks 

Hi @Anonymous,

Do you want to format the time column values (e.g. 'PT2H') from your SQL table within your app?

If you want to format the time column values from your SQL table within your app, please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/working-with-time-columns-in-sql-server/

If you have solved your problem, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous

 


Are you able to clarify what "replace 'ThisItem.Time' with the name of the value that contains the time column):" actually means? i assume "value" is incorrect and it needs the variable or data card?

I have 2 DC's and have tried updating the ThisItem.Time to the DC eg openFromDC.Time.


What this means is that it's necessary to replace ThisItem.Time with a reference to the field that contains your time data. So in the case of where you want to display OPEN_FROM, the formula you would use is this:

 

 Time(
        If(
            IsBlank(Find("H", ThisItem.OPEN_FROM)), // Check if there is an hour component
            0,                                 // If not, the value is zero
            Value(                             // Otherwise, take the substring between 'PT' and 'H'
                Mid(
                    ThisItem.OPEN_FROM,
                    3,
                    Find("H", ThisItem.OPEN_FROM) - 3))),
        If(
            IsBlank(Find("M", ThisItem.OPEN_FROM)), // Check if there is a minute component
            0,                                 // If not, the value is zero
            Value(                             // Otherwise take the substring between the rest of the value
                Mid(                           //     after the hour component and the 'M' indicator
                    ThisItem.OPEN_FROM,
                    If(
                        IsBlank(Find("H", ThisItem.OPEN_FROM)),
                        3,
                        Find("H", ThisItem.OPEN_FROM) + 1),
                    Find("M", ThisItem.OPEN_FROM) -
                        If(
                            IsBlank(Find("H", ThisItem.OPEN_FROM)),
                            3,
                            Find("H", ThisItem.OPEN_FROM) + 1)))),
        If(
            IsBlank(Find("S", ThisItem.OPEN_FROM)), // Check if there is a second component
            0,                                 // If not, the value is zero
            Value(                             // Otherwise take the substring after the minute or hour indicator,
                Substitute(                    //     remove the 'S' indicator, then take the value
                    Mid(
                        ThisItem.OPEN_FROM,
                        If(
                            !IsBlank(Find("M", ThisItem.OPEN_FROM)),
                                Find("M", ThisItem.OPEN_FROM) + 1,
                            !IsBlank(Find("H", ThisItem.OPEN_FROM)),
                                Find("H", ThisItem.OPEN_FROM) + 1,
                            3)),
                "S",
                ""))))

And in the case of OPEN_TO, you would replace ThisItem.OPEN_FROM with ThisItem.OPEN_TO.

 

 

 

 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,190)