cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

@Catmarch 

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

View solution in original post

7 REPLIES 7
Super User II
Super User II

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

New Member

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!

 

Super User II
Super User II

@Catmarch 

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

View solution in original post

New Member

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.

New Member

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

New Member

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!

 

Super User II
Super User II

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

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (61,265)