1. OCAU Merchandise is available! Check out our 20th Anniversary Mugs, Classic Logo Shirts and much more! Discussion in this thread.
    Dismiss Notice

Any tips on SharePoint and Power Automate?

Discussion in 'General Software' started by B3nj3, May 20, 2022.

  1. B3nj3

    B3nj3 Member

    Joined:
    Feb 26, 2005
    Messages:
    141
    Location:
    Brisbane
    Hello all!

    TL;DR – I’m looking for:
    • Create an Outlook event on a shared calendar which I do not own but have access to (Power Automate)
    • A function to match and return document in SharePoint (Power Automate)
    • Syntax for the equivalent of Excel’s VLOOKUP (SharePoint)
    Preface:
    Our company has just transitioned to a 100% cloud-based Microsoft 365 / Azure platform. We have the full battalion of Office 365 tools. It is an excellent set up, which we want to use to its full potential. Presently I’m exploring compliance and document control. I have a SharePoint site, and a list for this purpose.

    Point 1:
    I want to have a calendar on the site's home page with upcoming expiries of documents or training. There’s some great baked-in Power Automate templates. One Specific one is “Create Outlook calendar event on new item in SharePoint list”. I have custom columns in SharePoint for information like, last review date and next review date. I can add dynamic field to create the event based on the data in one of these columns. I wish to add calendar events to our team’s shared calendar, I have full access to this calendar, but I am not the owner. Is there a work around? I have used the template; however, it is not able to fetch our team’s calendar as it is not nested under “My Calendars”.

    Point 2:
    Within the body of the event, I wish to add dynamic content. Most of this is straight forward.
    [SP Document Name] is due for review on [SP Review Date]
    You can view the published version here: [SP Link]
    You can open the editable version here: [???]
    This is where I would need a match function. The published versions are .pdf, the editable versions are .docx. The editable versions are saved in a separate list, but with the exact same file name (except for the extension). Can anyone tell me if there is a search and return function? Ideally, I could plug in [SP Document Name] & “.docx”, point it at a different folder, and have it return [SP Editable Document]. Which I could then embed in the event.

    Point 3:

    Once I’ve homed in the first two points, I intend on applying the same approach for staff training, and site audits. Each of the items we measure will have varying expiry dates. For simplicity, when an operator adds a scanned certificate / license to the folder, I want them to only: 1) declare the type of document it is (custom choice column), and 2) declare the date it was obtained. The validity period should be automatically calculated based on a table / spreadsheet. E.g. The document is a First Aid certificate, therefore, the validity period is 3 years. The expiry date column will be automatically populated with [Date Obtained] + [Validity Period]. Can you offer any guidance on doing a look up function? Is there any way the table specifically needs to be formatted, or where it should be saved?

    Thank you all in advance for your help.
     
  2. Elmf

    Elmf Member

    Joined:
    Jan 6, 2007
    Messages:
    2,770
    Location:
    Melbourne
    I've managed the deployment of a 'not half arsed' document management solution using what you have listed above.

    It works, but I wouldn't recommend doing it. Use a proper out of the box document management solution for formal docs that have expiries etc.

    Just because you can do it, doesn't mean you should.
     

Share This Page

Advertisement: