PA with Google Sheets and auto numbering/primary key

[Tech08]

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

[Tech08]

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.

[mr-dang · Accepted solution]

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.

[Tech08]

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.

[Tech08]

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!

[mr-dang]

---

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