cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
David_G
Level: Powered On

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
Super User
Super User

Re: Help with passing variables between queries & forms

Hi @David_G

 


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
Super User
Super User

Re: Help with passing variables between queries & forms

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.
David_G
Level: Powered On

Re: Help with passing variables between queries & forms

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??

 

 

Community Support Team
Community Support Team

Re: Help with passing variables between queries & forms

Hi @David_G,

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.
Super User
Super User

Re: Help with passing variables between queries & forms

Hi @David_G

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/...

 

Highlighted
David_G
Level: Powered On

Re: Help with passing variables between queries & forms

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

David_G
Level: Powered On

Re: Help with passing variables between queries & forms

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 

Community Support Team
Community Support Team

Re: Help with passing variables between queries & forms

Hi @David_G,

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.
Super User
Super User

Re: Help with passing variables between queries & forms

Hi @David_G

 


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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 233 members 2,388 guests
Please welcome our newest community members: