Automatically Numbering Excel Line when form is submitted

[RSygroves]

Hello,

I don't find how to solve the following issue.

Here's some context : I made an app that allows employees to declare dysfunctions by completing a form. 

Then the few people who have to manage those dysfunctions can have another screen in which they have a overview of all the declarations and can edit them in another screen with more fields. All datas are stored in an excel table.

No problem with the app, it works fine.

But, for each line added to the excel table, I have a column corresponding to the declaration number.

As far as the app doesn't support formulas within the excel table, what can I do to have each new line automatically numbered when the corresponding form is submitted ?   

For now, I go in the excel table and write manually, so that every declaration in the overview screen has its own number, but it's not satisfying.

Best regards,

Romain

[v-jefferni]

Hi @RSygroves,

Do you want to update a number field automatically when the corresponding form is submitted?

The number is different from those of other rows in the excel table?

Based on your description, I have made a simple test for your reference.

Here is my excel table:

Formula for ‘Default’ of DataCardValue control: 

If(EditForm1.Mode = FormMode.New,Max(TableName,ID)+1,Parent.Default)

Result is as follows, when creating an record, ID will generate automatically. The value of the ID is 1 greater than the maximum in table.

Best regards,

[RSygroves]

Hello @v-jefferni 

thanks for your answer.

I tried your solution.

I don't get any error while writing the formula, but it seems it can't retrieve the existing values and set the new form as "1".

When submitting the form, I don't get any number in the table....

My submit button has an OnSelect property set to Patch(...   so maybe it isn't writtent correctly as regards my "number" datacard.

[v-jefferni]

Hi @RSygroves ,

Usually we set OnSelect property to 'SubmitForm(EditForm)', and all data will be added. If you want to use Patch, you need to add the number field in your formula. Could you please share the whole formula currently you use as well as a screenshot of the headers/column names of the Excel table?

Best regards,

[RSygroves]

Hi @v-jefferni 

actually I can't remember why but I had no choice to set this Patch formula as SubmitForm didn't work correctly.

Here it is :

Patch(
Synthèse;
Defaults( Synthèse );
{
Antenne: AntenneValue.Selected.Antenne;
'Nom du déclarant': 'Utilisateursd’Office365'.MyProfile().DisplayName;
Statut: StatutValue.Selected.Statut;
'Date évènement': DateValue.SelectedDate;
Nature: NatureValue.Selected.Nature;
'Problème rencontré': ProblèmeValue.Selected.'Problème rencontré';
'Formation concernée': FormationValue.Text;
Lieu: LieuValue.Text;
'Description / Commentaires': DescriptionValue.Text;
Etat: EtatValue.Text;
'Date enregistrement': DateRecValue.SelectedDate
}
)

 and here is the excel table :

[v-jefferni · Accepted solution]

Hi @RSygroves ,

The Numéro field needs to be added to the formula.
You could try the following formula:

Patch(
Synthèse,
Defaults( Synthèse ),
{
Numéro: Max(Synthèse,Numéro)+1,
Antenne: AntenneValue.Selected.Antenne,
'Nom du déclarant': 'Utilisateursd’Office365'.MyProfile().DisplayName,
Statut: StatutValue.Selected.Statut,
'Date évènement': DateValue.SelectedDate,
Nature: NatureValue.Selected.Nature,
'Problème rencontré': ProblèmeValue.Selected.'Problème rencontré',
'Formation concernée': FormationValue.Text,
Lieu: LieuValue.Text,
'Description / Commentaires': DescriptionValue.Text,
Etat: EtatValue.Text,
'Date enregistrement': DateRecValue.SelectedDate
}
)

Best regards,

[RSygroves]

Thank you very much @v-jefferni 

It works perfectly, actually I didn't add the Numéro field correctly to the formula.

Best regards

Romain