Control when PowerApps queries the data source

[Caz_16]

I have looked through the documentation on this one, and I cannot find any assistance on this, hopefully someone can help me out. 

I have 2 "Employee" datasets, one that I store in SharePoint and the other on an Oracle server. Most of the day-to-day stuff we need is stored on SharePoint, and that is the info that my users are most likely to need when running the app, which is why I am storing a sub-set of the data there. When we need additional info though, we have to turn to the Oracle server. 

The SharePoint list query time floats around 750-1000ms time to results returned. The Oracle server is up around the 5000-5500ms time. Each time I click on a different person in my gallery to populate my "Employee Edit Form", I have 5 seconds of "ants" at the top of my screen while it retrieves data from the Oracle server to populate the form for my selected item.

So without having to make another screen, I was wondering if there was a way to control when my app asks the Oracle server for data? I tried using the code below to keep the value hidden and return blank if the card was not visible, but in looking at the monitor, the app is still asking for the value from the db despite the card not being visible (this is in testing, so this is the only card that is asking for data from the Oracle server, there are no other cards that it would be asking the server for). I have a button that changes a variable to make the card visible. 

If(
    Self.Visible,
    Text(
    If(
        NewEmployee = true,
        Blank(),
        LookUp(
            'HR.JOBS_CURRENT',
            ENTITY_KEY = ThisItem.Entity_Key,
            ORG_CODE_DESC
        )
    )
)

So how do I have PowerApps ask SharePoint for data, but hold off on requesting info from Oracle until told to do so? 

Thanks

Caz

[Silvester]

What is your structure? I think to avoid calls to the Oracle dB, you must limit or scope your search to SharePoint(since you have minimal data in there), if there is a need to have additional or extra data from Oracle, you will add a button to request it.
Basically, the call happens unless i click the button. 

[Caz_16]

@Silvester ,

I agree, limiting the scope is what I am trying to do. Can you provide any insight into how I would go about creating a button that only requests data when selected?

Data Structure Outline

SharePoint List Columns:

Person ID

Name

Oracle ID

Division

Group

Position

... and so on, there are other columns, not important to the example. 

Oracle ID	Name	Salary	Org

... And so on, this table is huge. 

So when i need data from Oracle, I use ThisItem.OracleID to do a request to the oracle Warehouse for the fields I want.

The problem is that each time I click on a person in my gallery, it does the request to Oracle. I want to be able to click on a name in the gallery, have it pull that person's info from SharePoint, but not do the Lookup and request from Oracle unless I ask it to. 

Hope this provides some clarity

Caz

[Silvester]

Alright @Caz_16 , 
It is more clearer, On the gallery(make sure your gallery datasource is only SharePoint) set the OnSelect property to a variable

Set(VarEmpSP, ThisItem)

Then use it in the Item property of the EditForm or ViewForm or wherever you need it. This should only request from SharePoint, You can also add another Button (You could name how you want or use an icon), set the OnSelect property of the button to 

Set(VarEmpSP,LookUp(OracleDb,ID=ThisItem.OracleID))

 Accept this as a solution if it works

[Caz_16]

I can confirm:
 - My Gallery is only set to SharePoint  (its actually a collection, we have less than 500 employees, so I do a collect to get the columns from the SharePoint List I need on to device to decrease latency). 

- The only property that calls the Oracle DB is the "Default" property of the "Organization" Card of my form. 

- I am using a variable to control the "Visible" property of the card the LookUp resides in. 

Here is a diagram of my logic. 

So in my mind, the App shouldn't ask the Oracle DB for the ORG_DESC value, unless the card is Visible. The card is only visible if the variable controlled by the button is set to True, yet every time that i click on a different gallery Item, Activity Monitor shows that I hit the Oracle DB asking for that value. 

Im thinking it has something to do with the fact that it is within a form maybe?

Thanks

Caz

[Caz_16]

Also I forgot to mention, that I have control of the _ShowCard variable set to be changed back to False when selecting a new person. I have a Label showing the value of that variable so I can monitor it when testing and make sure that it is changing only when I want it to. 

[Silvester]

Yeah, it is because it is used on a card which is part of a form.
Kindly follow the steps i suggested earlier. It might alter your design a bit but you should be fine.
The alternative to using the Card visible property (which i don't really trust much in these kind of scenarios) is to use a button i suggested earlier, now we can go an extra step to set the visible property of the button based on how you want it, such a way that it is only visible when a condition is met, exactly according to your scenario.

[Caz_16 · Accepted solution]

@Silvester 

Ill be honest, your answer wasn't expressly clear, but I think I understood enough of it and here is the solution that I came up with. 

My form Item property is still set to ThisItem. I don't like having forms work on Records stored within Variables. I've given myself issues in the past with that, so I always just keep it on ThisItem. 

What I did do however, was use your suggestion of setting the OnSelect property of my Gallery to set a variable:

Set(_EmployeeSelected, ThisItem). 

I have a button outside of my form that has the OnSelect property set to:
OnSelect = Set(_DownloadedData, LookUp(OracleDB, OracleID = _EmployeeSelected.OracleID)

This is where I am storing the entire record returned from Oracle in that variable. 

Then in my form, for all the fields that I want to pull from the database, I set the Default property of the card to _DownloadedData.[Whatever Field I Wanted to See]. So if _DownloadedData is blank, then they just show nothing, which is fine because we don't modify those records, and we only occasionally want to see what they are. 

I have logic throughout to clear the _DownloadedData and _EmployeeSelected variables back to nothing when they are not in use. 

Now when I click my "Download" button, it returns the fields I want from the Oracle DB into my form. 

Thanks for your help. 

-Caz

[Silvester]

Sure, the most important is you getting it done. Can you please accept as a solution to close this thread? So it is available for other users too? Do a give a thumbs up as well if you find it interesting.