Help with passing variables between queries & forms

[Anonymous]

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 

[Drrickryp]

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]

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]

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

[timl]

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]

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]

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 

[v-xida-msft]

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

[timl · Accepted solution]

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.