Import Data using Excel/PDF in Power Apps

[Sidhant_02]

Hi everyone,
Currently to take in user inputs for registration purpose I am using a simple form structure where in user can enter the details as required. 
For example: Qualification

There is a gallery wherein I am showing all the data that the user has entered:

Now to make this more simpler what I want to do is add a button named Import from File, where in there will be the qualifications details for the user and using the file that is uploaded it should save all the details similar how we did using the form (which should be associated only for the current user not others: for which in case of forms I have used the MS365.Id and the Employee Id field).

Filter(SkillMatrix_Certifications,EmployeeId= Office365Users.MyProfile().Id)

My form has multiple sections like certification details, project details, skills, qualifications. So currently I am trying to import one section from the file, but later on if we have to import all the data in their respective field like qualifications should populate in its field not others, how to do that (is it part of AI builder) - {this can be done later}
For now I am focusing one part (qualifications}, in this case if anyone has any idea on how to solve this please do reply it will be helpful.

Regards,
Sidhant.

[Sidhant_02]

Hi @Pstork1 ,
Thanks for the response, I have done the following
1. On the gallery to display records for the current user I have added the following expression

Filter(Details,'Created By'.Email = User().Email)

(Here Details is the name of the SharePoint list, where all the records are uploaded)

2. To avoid duplicate uploads by any user ( one user should upload a file once not more than that), for that I created a local variable that checks in the SharePoint list if there any more records modified by a user or not based on that perform Patch

(Is this correct, and how to manage the delegation issue), if not could you please give an example (by referring the information provided on how to check for duplication upload by a user)

3. To download the template file, as you mentioned I uploaded the template file with the headers (in the Sharepoint document library) 

And added a button using launch (mentioned the file URL), from where user can save a copy of the file in their system. I did find a work around wherein we can download the file directly on-click of the button from Power Apps (the file won't be opening in a new tab) for that the Syntax was:
Launch("https://Site_Url/Document_Library_Name/FileName.xlsx")
But this was not working, it displayed file not found

And the other thing you mentioned to check if the file is in the same template format to add a condition before or after List rows (split) could you give an example on how to do the check, it will help.


Regards,
Sidhant.

[Pstork1]

1) Yes, that's exactly what I suggested

2) Instead of using a CountIf() just do a Lookup(). If the result is null then no record exists.   Also, instead of using User().email in the function save that as a variable first to avoid delegation.

3) Make sure you replace all the spaces in the URL with %20. See my example below.

Launch("https://acmee3.sharepoint.com/sites/FlowDemos/Shared%20Documents/Filename.xlsx")

[Sidhant_02]

Hi @Pstork1 ,
Thanks for confirming. Regarding replacing the whitespaces as my SharePoint library does not have any spaces in between (previously it was named: Iz Doc to avoid spacing issue I renamed it to IzDoc) so should I still add the 'Iz%20Doc'.

Made the change: 

Launch("https://iz.sharepoint.com/sites/IntelizignPune/Iz%20Doc/excelImportTemplate.xlsx")

Now on click initially it shows the page 'Did not find the page, check spelling for 2 seconds' and then starts the download but get this screen:

So I uploaded the file in Documents (Shared Documents - system default name} and used the following:

Launch("https://iz.sharepoint.com/sites/IntelizignPune/Shared%20Documents/excelImportTemplate.xlsx")

This works fine

- For your second point about the condition for duplication check is this correct:

//Storing the user.email in a variable:
UpdateContext({lclUserMail: User().Email});

//Duplication Check
UpdateContext({lclRecordCheck: LookUp(Details, 'Modified By'.Email <> lclUserMail, true)});

If(lclRecordCheck,

ForAll(Details,
    Patch(Employee_Projects,
    Defaults(Employee_Projects),
    {
        Title:ThisRecord.Project1,
        Description:ThisRecord.Description1,
        EmployeeId:Office365Users.MyProfile().Id
    }
    )
),
Notify("Duplicate Record, one user can only upload one file",NotificationType.Information)
)

- And the other thing that I had doubt using 'Modified By ' how can we determine that a particular user is  not imported data from excel more than once (as modified by generally tracks changes made to a record)

- Also for the template check you had mentioned to have a check in the flow could you explain in the context of my example.

Regards,
Sidhant.

[Sidhant_02]

Hi @Pstork1 ,
Earlier to patch the records associate with a user I was using:

For which you suggested to make use of email instead of Id, but later on when there will be more than one record or row in the SP list like:

So in that case the Patch statement that I have used will it work & how will it differentiate which is record is for which user and according patch, I was thinking of the : 'Office365Users.MyProfile().Id' but how this can be used while mapping the data in the Patch statement is where I am confused
(This is the current Patch statement: wherein as discussed we are checking for duplicate entries by a user)

//For Duplicates:
UpdateContext({lclRecordCheck: LookUp(Details, 'Modified By'.Email <> lclUserMail, true)});

UpdateContext({lclRecordsCount: CountIf(Details,'Modified By'.Email = User().Email)});




If(lclRecordCheck,

ForAll(Details,
    Patch(Employee_Projects,
    Defaults(Employee_Projects),
    {
        Title:ThisRecord.Project1,
        Description:ThisRecord.Description1,
        EmployeeId:Office365Users.MyProfile().Id
    }
    )
),
Notify("Duplicate Record, one user can only upload one file",NotificationType.Information)
)

So now if had to also map record details of different users how to do that.

Regards,
Sidhant.

[Pstork1]

You can change the display name, but that doesn't change the actual url. It will still have spaces, so yes you need to replace those.

Not sure why the login prompt is coming up, but you do have to be logged in to download the file.

For the second part I was suggesting this

UpdateContext({lclUserMail: User().Email});
If(
    IsBlankOrError(
        LookUp(
            Details, 
            'Modified By'.Email = lclUserMail,
            Title
        )
    ),
    UpdateContext({lclRecordCheck: 0}),
    UpdateContext({lclRecordCheck: 1})
)

if lclRecordCheck is zero there is no duplicate. If its 1 there is.

Using Modified by means that the user uploading a second file will be the same as uploading the same file twice. You said that a user should only have one entry.

[Pstork1]

Different users will generate different Created By and Modified By entries in SharePoint. You can use that and the user().Email to track which user uploaded information for which record.  Weve already discussed that several times. Its the same as using the ID, but you don't need to use Office365Users to get the ID because the email is already available.

[Sidhant_02]

Hi @Pstork1 ,
I know you have mentioned this in your replies previously, but it will be helpful if you can provide an expression for the same (like how you shared for the duplicate one as per my data-source)

And on using the user.mail() when we have more than 1 record in the SharePoint list (which stores the data extracted from the excel uploaded), do we need to make changes to the Patch statement because I am thinking ThisRecord will point to the very first row and if data like this (or is it something else):

(Just as reference assuming there are more than 1 row, so in that case how should we tell ThisRecord to refer to particular row, which you suggested)
If possible could you give an example w.r.t my context (from the information I have shared earlier)

I tried this, but I guess this is not right:

Regards,
Sidhant.

[Pstork1]

ThisRecord doesn't really mean anything outside of a ForAll() function. In the ForAll function it refers to the current record that the formula is being applied to. I don't see any ForAll in your code and I think that is why its throwing an error.  But the main point is What are you trying to patch? ForAll only works if you are updating a set of records with a formula.  I've also already explained that if you use the user's email, which is already there, then you don't need the Account ID. But that appears to be the only thing this formula is updating. So why do you even need it?

[Sidhant_02]

Hi @Pstork1 ,
I guess the ForAll was not visible due to the error message, the expression was as follows:

If(
    IsBlankOrError(
        LookUp(
            Details, 
            'Modified By'.Email = lclUserMail,
            Title
        )
    ),
    UpdateContext({lclRecordCheck: 0}),
    UpdateContext({lclRecordCheck: 1})
);




If(lclRecordCheck  = 0,

ForAll(Details,
    
    Patch(Employee_Projects,
    Defaults(Employee_Projects),
    {
        Title:ThisRecord.Project1,
        Description:ThisRecord.Description1,
        EmployeeId:Office365Users.MyProfile().Mail
    }
    )
    
),
Notify("Duplicate Record, one user can only upload one file",NotificationType.Information)
)

So now before patching the Projects data we need to perform a check that only save the data that is created by the current user (and not others data), so the check condition where should that be placed and I have removed the MyProfile.Id() as you said and instead of that I am making use of the mail field so is that what you were asking for.

Also in one of your replies you had mentioned for the template check that has the user used the same template that we have provided has entered the data in it, if they have used the same template as provided then proceed with the normal flow or else if the user has used some other file then stop the process and notify the user 'Please use the Template format' so for that how the check will be added in the flow can you give an example.

Regards,
Sidhant.

[Pstork1]

1) As I mentioned in my last reply you don't need the patch at all if the only unique value you are patching is the account ID. The user's created by email is there automatically and fulfills the same purpose.

2) If a user can have multiple records then tracking things by the email alone, or the account ID, isn't enough. You need to have a way to access each individual record uniquely.

3) If the user uses a table that doesn't match your schema then it will throw errors when you try to read it or certain columns won't be there.  So add a parallel branch to the flow after the list rows action and set it to run if the list rows action throws an error.