I currently have an SQL database out in the cloud with a hosted provider. They seem to go down several times a year so I decided to use the Gateway Connector and move it on prem. Gateway connector seems to be working fine, I can see all my databases (I've tried windows auth and SQL auth per my reading sometimes windows auth doesn't function properly).
I know after reading that there is no straight forward way to migrate from one data source to another but what I've tried is to open my prod app, save as a copy and work in the copy for testing.
I started with the main gallery on the first screen which is just a simple browse screen. The current code for the cloud database that has worked for years now is:
SortByColumns(Search(Filter([@'plumbing.dailyLog'],Date=Today()), TextSearchBox1.Text, Address,Twp,Contractor,Permit,Type,Inspector), "Order", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))
I simply changed the [@'plumbing.dailyLog'] to [@'dailyLog'] and I get no errors but no data displays in my gallery. I can click on [@'dailyLog'] in the formula and click the down arrow and it does populate data but it appears the formula is incorrect but again no errors. I've tried manipulating it 100 different ways and it does not work. If I just put [@'dailyLog'] in the formula it displays all my data. They are both SQL servers. The databases are identical. I have a second screen that is a datepicker so Date=Datepicker and it doesn't work either thinking the Date was the issue but I don't believe it is.
Breaking it down a little more.....
Filter([@'plumbing.dailyLog'],Date=Today())
- This works....
Filter([@'dailyLog'],Date=Today())
- This does not...
dailyLog
- just this populates all data for the new database
'plumbing.dailyLog'
- This works too for the old database
Is it something with the date field and the new database, both are date fields in SQL. The cloud is on SQL 2017 and were on SQL 2022 locally. I've tried creating a new database from scratch instead of import and export.
Any help would be much appreciated....
