Relational Tables in Excel - ID fields won't remain integer data types

[Catmarch]

I am trying to avoid paying for the premium subscription of power apps.  However, it is beginning to look like I will have to pay the premium in order to use SQL, where the data types in the tables should remain stable.

I have setup some excel tables in one drive.  I am noticing that ID fields I have designated in the excel tables as integers (number with no decimals) will revert back to general data type.  This makes the relationships not work anymore bc some of the tables ID fields are now "text" and some of the ID fields are now "number".  I need to have these ID fields integers so that i can use a patch to add new records with the next ID. 

I have gone back to the excel tables and reformatted all of those ID fields as integer (number with no decimals), renamed the fields, removed the datasource from power apps and re-added it.  It seems to work for a while, ie, that that ID data types remain "integers".

However, it has happened now numerous times that the data types for the "ID" fields in the excel tables have reverted back to "general" type.  It seems like the most recent trigger for this was when I added a new data source to the app.  It seemed to have reset all the "ID" fields in the other data sources back to "general" data type.

I did a general search and I couldn't find anything indicated that other people have had this problem.  Should i be using the powerapps ID for this purpose?  I am extremely frustrated.  I just need to know if it is not possible to use one drive excel tables to build a relational db before i go to my boss for money.  It certainly appears that you need to pay a premium in order to design a front end in power apps that depends on a relational set of tables.  Can anyone confirm?  Thank you!

[Drrickryp]

@Catmarch 

If you multiply a text value in PowerApps by 1, it converts it to a number automatically.  In fact, although this is an undocumented feature, any arithmetic operator will convert text to number in PowerApps.

You could make it a habit to do *1 in all your formulas. However, Excel is not a good datasource for a production app but it could be useful to show your boss as a proof of concept so you could get a budget for SharePoint or Dataverse.

[Catmarch]

thank you, that's a good idea, gonna try that *1 now.  And thank you for confirming that shared excel files are not a great datasource!

[Drrickryp · Accepted solution]

@Catmarch 

Check out this short video by Shane Young about why Excel is a Terrible datasource.   https://www.youtube.com/watch?v=RtmZM3tm2Zs 

[Catmarch]

ok i will.  LOL, i hate excel, and i really don't trust it as a data source (i'm a SQL/Access OG) but like i was saying, just trying to save money.  BTW, i just tried the *1 on one of those ID fields and it isn't working.  but i'm going to experiment a little more.

[Catmarch]

@Drrickryp just watched the vid - that guy Shane is very helpful; i've watched other of his vids.  Before i start on the sharepoint route, do you think that will fix the ID problem that i'm having?

[Catmarch]

i've tried the sharepoint and there is an auto id available in those lists, so i think my problem is now temporarily solved, until Santa brings us premium.  thank you!

[Drrickryp]

@Catmarch 

Before  you get too far into SharePoint, it is important to understand how it interacts with PowerApps. IMHO, @WarrenBelz has the single best resource to check so you can avoid the pitfalls.   https://www.practicalpowerapps.com/   It should be required reading before you start with SharePoint.