cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate V
Advocate V

PA with Google Sheets and auto numbering/primary key

I am creating a ticketing system now between PA and Google Sheets and I'm trying to create a primary key of sorts that is unique for ticket numbers.  How can I accomplish this?  I would use a Google add on, but copydown only works when a Google form is dumping the data.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: PA with Google Sheets and auto numbering/primary key

Hi,

I had the same dilemma in the past. I was using Dropbox and it inevitably resulted in multiple records with non-unique primary keys.

 

Here are some things I had tried:

  • I placed my students in rotating small groups so that there would be fewer at the station using the apps at the same time to try to avoid saving over one another, but it didn't work out.
  • I tried identifying the maximum record prior to getting to the spot when filling out the form, but two users identified the same id yet saved at different times.
  • I wrote all the other fields, then went back to update just the id number, but this required two writes. It mostly worked, but this was 20s per Patch at the time since I was using Dropbox. It fell apart when I hit 500 records since Max() is an aggregate function that isn't able to delegate yet.
  • I switched to Common Data Service, which worked out since it was automatic and guaranteed to produce unique values.

 

Google Sheets is pretty fast at writing, but do be warned if you access the datasource too much, you may hit one of their quotas--either by volume or frequency. So if you want to proceed, the following might work out with minimal chance of error, but the chance is still there. The following solution will work if you do not expect more than 500 records in your datasource.

 

The idea is that you will Patch the record as usual (red), but keep the field for your primary key blank. You will collect the result of the patching to a variable (blue). Then you will use the variable to identify the record in the datasource you want to edit again so that you can apply a primary key after the fact (green). You can refresh before you calculate that primary key to have more certainty.

 

So your formula might end up looking like this:

Set(temprecord,
    Patch(datasource,Defaults(datasource),
        {field1: data,
            field2: data
        }
    )
);

Refresh(datasource);

Patch(datasource,temprecord,
    {primarykey: Max(datasource,primarykey)+1
    }
)

Let me know how this works out for you.

Microsoft Employee
@8bitclassroom

View solution in original post

5 REPLIES 5
Highlighted
Advocate V
Advocate V

Re: PA with Google Sheets and auto numbering/primary key

I think I found my simple solution of setting the ticket field to Max(Powerapp,Ticket)+1, but my only issue with that now is how do I make it refresh that result when the person submits the ticket to ensure there aren't duplicate numbers from people creating tickets at the same time?  If I tell it to refresh the data source it clears the values of all the fields before it submits.  All I want to refresh is the one value on the data card.

Highlighted
Community Champion
Community Champion

Re: PA with Google Sheets and auto numbering/primary key

Hi,

I had the same dilemma in the past. I was using Dropbox and it inevitably resulted in multiple records with non-unique primary keys.

 

Here are some things I had tried:

  • I placed my students in rotating small groups so that there would be fewer at the station using the apps at the same time to try to avoid saving over one another, but it didn't work out.
  • I tried identifying the maximum record prior to getting to the spot when filling out the form, but two users identified the same id yet saved at different times.
  • I wrote all the other fields, then went back to update just the id number, but this required two writes. It mostly worked, but this was 20s per Patch at the time since I was using Dropbox. It fell apart when I hit 500 records since Max() is an aggregate function that isn't able to delegate yet.
  • I switched to Common Data Service, which worked out since it was automatic and guaranteed to produce unique values.

 

Google Sheets is pretty fast at writing, but do be warned if you access the datasource too much, you may hit one of their quotas--either by volume or frequency. So if you want to proceed, the following might work out with minimal chance of error, but the chance is still there. The following solution will work if you do not expect more than 500 records in your datasource.

 

The idea is that you will Patch the record as usual (red), but keep the field for your primary key blank. You will collect the result of the patching to a variable (blue). Then you will use the variable to identify the record in the datasource you want to edit again so that you can apply a primary key after the fact (green). You can refresh before you calculate that primary key to have more certainty.

 

So your formula might end up looking like this:

Set(temprecord,
    Patch(datasource,Defaults(datasource),
        {field1: data,
            field2: data
        }
    )
);

Refresh(datasource);

Patch(datasource,temprecord,
    {primarykey: Max(datasource,primarykey)+1
    }
)

Let me know how this works out for you.

Microsoft Employee
@8bitclassroom

View solution in original post

Highlighted
Advocate V
Advocate V

Re: PA with Google Sheets and auto numbering/primary key

I'm not sure how Dropbox works as a datasource for PA, but I started initially with Sharepoint as that is what we are used to for a ticket system and because it is first and foremost a powerful Microsoft product, so you think it would mesh well.  I was doing good building it until I realized the filtering and searching wasn't going to work out because it couldn't delegate the functions to Sharepoint.  Due to the possible databases I then went to Google as we are a Google district and it handles my functions very well and have a searchable gallery with 3 dropdowns and a search that all work together.  Go Google!  The past few weeks PA and Google weren't working well together as I couldn't add new columns or attach any other data sources for some reason, so I started looking at SQL of some sort, which I know nothing of how to setup or run.  Anyway today I realized PA got updated and all was well again with Google.

 

Sorry for the rant, but all in all I went with Google because delegation doesn't appear to be an issue where I should be limited to 500 like with Sharepoint...or at least PA isn't telling me it will be an issue.  We are a smaller district and I wouldn't expect more than 20-50 tickets entered a day max.

 

On your patch you have your formula for the max which I am surprised by as I tried to do that in an update context command and it wouldn't do it.  I'm trying to keep my formulas as simplified as I can and I have a lot of fields that are being submitted.  I can give your way a shot and see what happens.  It will be my first shot at a patch.

Highlighted
Advocate V
Advocate V

Re: PA with Google Sheets and auto numbering/primary key

Took me several tries to figure out how to form the patch and make it work with a couple fields, but I think I figured it out and looks like it should work.  It will be a long formula for the submit button, but if it works, that's what matters.

 

Thanks!

Highlighted
Community Champion
Community Champion

Re: PA with Google Sheets and auto numbering/primary key


@Tech08 wrote:

On your patch you have your formula for the max which I am surprised by as I tried to do that in an update context command and it wouldn't do it.  I'm trying to keep my formulas as simplified as I can and I have a lot of fields that are being submitted.  I can give your way a shot and see what happens.  It will be my first shot at a patch.


 

If it doesn't work, it could be because all fields in Google Sheets are treated as text, so you would need to convert what you read and write to text:

 

Text(Max(datasource,Value(primarykey))+1)

That will likely bring up a blue dot about delegation, but if you will stay below 500 records, it should manage fine.

Microsoft Employee
@8bitclassroom

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,825)