PDF Function in PowerApps to create PDF Document in SharePoint library and Email as attachment

1. Add PDF Document icon on screen
2. Add following code OnSelect property of PDF Document icon.
3. • Set(varPDFDoc, PDF(EditScreen,{ExpandContainers:true}));
   • Set(varlogDate,Text(LDDataCardValue.SelectedDate,”yyyy-mm-dd”));
   • Set(varFileName,Concatenate(varArea,”-“,varlogDate,”-“,DNDropdown.Selected.Value,”.pdf”));
   • FlowName.Run(varFileName, varArea,Now(),User().FullName,{file:{name:varFileName,contentBytes:varPDFDoc}});
4. Add Select(PDFIcon) on Save/Submit button (to trigger the flow).
5. Create workflow.
6. Add flow to PowerApps application.
   • Add PowerApps v2 trigger in flow
   • Add parameters and then add create file actions shown in pics.

7. Send an email from PowerApps

Office365Outlook.SendEmail(User().Email, “”,””,{Attachments: Table({Name: “PDFName.pdf”,ContentBytes:PDF(NewForm)})});

NOTE: Another way to get file name is:

@{triggerBody()?[‘file’]?[‘name’]}

Advertisement

Useful PowerShell scripts for PowerApps

Before proceeding towards PowerShell scripts for PowerApps & Power Automate, follow below steps.

Launch windows powershell as an admin.

Run the below commads.

Install-Module -Name Microsoft.PowerApps.Administration.PowerShell
Install-Module -Name Microsoft.PowerApps.PowerShell -AllowClobber
Type A, accept all the installation policies and then connect to your environment. Use below command for the same.

Here is how you can pass in credentials (avoiding opening a prompt)

$pass = ConvertTo-SecureString “password” -AsPlainText -Force
Add-PowerAppsAccount -Username admin@admin.com -Password $pass

Update schema of Table/View

ALTER SCHEMA New_SchemaName TRANSFER [dbo].vwPerson_Details

Split the string /Split the column value

a. The following statement splits the ColumnA value into rows and saves values into SplitValue column

Select pd.[ColumnA], TRIM(value) as SplitValue from Person_Detail pd CROSS APPLY string_split(pd.[ColumnA], ‘,’)

EX: If ColumnA value is C+AI, Azure CXP, EMEA then the above statement gives the following results.

b. The following statement splits ColumnA value and saves the substring after FIRST comma (“,”) into SplitValue column if ColumnA contains comma (‘,’) otherwise it saves the string into SplitValue

Select pd.[ColumnA], TRIM(SUBSTRING(pd.[ColumnA],charindex(‘,’,pd.[ColumnA])+1,len(pd.[ColumnA]))) as SplitValue from Person_Detail pd

Ex:

Add a blank value to drop down list in PowerApps

To add a default blank value to drop down list

Ungroup(Table({menuOptions: Table({Result:””})},{menuOptions: Sort(Distinct(‘SourceName’, ColumnName).Result,Result)}),”menuOptions”)

Find most recent record of each employee

SELECT t1.*
FROM
[empDetails] t1
INNER JOIN
(
SELECT Alias, MAX(ModifiedDateTime) AS MaxDate
FROM [empDetails]
GROUP BY Alias
) t2
ON t1.Alias=t2.Alias

        AND t1.ModifiedDateTime=t2.MaxDate order by Alias, ModifiedDateTime DESC

Convert a date from a text box into date in Powerapps

SubmittedDate=DateAdd(DateValue(LabelSubmittedDate.Text), “mm/dd/yyyy”)

Concatenate PowerApps Gallery Items and display in a HTML Textbox with Line breaks

HTML text input box in PowerApps doesn’t recognize Char(10) and Char(13) to provide the space and line breaks. So, I had to use the Substitute function to get line breaks in HTML Text input box.

Substitute( TextInput1.Text, Char(13), “<br/>”)

My scenario was:

Concatenate values (Column1 and Column2) from PowerApps Gallery and display in HTML Text with line breaks for each record and Column1 in bold.

Set(varGalleryItemsConcat, Concat(Gallery.AllItems,Substitute(Concatenate(““, Column1.Text,”“, Char(13)&Column2.Text&Char(13)&Char(13)),Char(13),”
“)))

Add varGalleryItemsConcat variable to HTMLText property

You will see the data HTML text input box as follows. Let’s assume there are 3 rows in gallery

Column1 row1 value Column2 row1 value

Column1 row2 value Column2 row2 value

Column1 row3 value Column2 row3 value

PowerApps – Add custom menu options to a dropdown box

To add custom menu options to a dropdown box which is pulling dropdown values from data source.

Ungroup(Table({menuOptions: Table({Result:”All”})},{menuOptions: Sort(Distinct(DataSourceName, ColumnName).Result,Result)}),”menuOptions”)

SQL Tables schema

To get Table and column names

SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(‘FlatFileDataHolder’)

To get Table and column names and data types

select col.name as ‘ColumName’,typ.name as ‘DataType’ from sys.columns col left join sys.types typ
on col.system_type_id=typ.system_type_id where object_id=object_id(‘FlatFileDataHolder’)

To get all tables and their schema

SELECT * FROM INFORMATION_SCHEMA.COLUMNS