tag:blogger.com,1999:blog-9654478203455817932024-03-16T11:52:20.770-07:00SharePoint ConnoisseurHarry Chen's SharePoint tricks and tipsHarry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.comBlogger310125tag:blogger.com,1999:blog-965447820345581793.post-40964694942094353712020-10-23T14:58:00.002-07:002020-10-23T14:58:36.964-07:00<p style="text-align: justify;"><span style="font-size: medium;"><span style="background-color: white; color: #171717;">In Power BI dataset, you could use PowerShell to refresh
the dataset. Here is the sample script to refresh </span><span style="background: white; color: #171717;">Here
is the script to refresh dataset named “</span><b style="background-color: white; color: #171717;">MyReport</b><span style="background-color: white; color: #171717;">” inside workspace
named “</span><b style="background-color: white; color: #171717;">My Workspace</b><span style="background-color: white; color: #171717;">”. </span> </span></p><p><span style="font-size: x-small;">$User = "admin@mycompany.com"</span></p><p><span style="font-size: x-small;">$PW = "password"</span></p><p><span style="font-size: x-small;">$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force</span></p><p><span style="font-size: x-small;">$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)</span></p><p><span style="font-size: x-small;">Connect-PowerBIServiceAccount -Credential $myCred</span></p><p><span style="font-size: x-small;"><br /></span></p><p><span style="font-size: x-small;"># Get Power BI Workspace ID</span></p><p><span style="font-size: x-small;">$WSID = Get-PowerBIWorkspace -Scope Organization -Name 'My Workspace'| ForEach {$_.Id}</span></p><p><span style="font-size: x-small;"><br /></span></p><p><span style="font-size: x-small;"># Get dataset ID</span></p><p><span style="font-size: x-small;">$DSIDRefresh = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | Where {$_.Name -eq "MyReport"} | ForEach {$_.Id}</span></p><p><span style="font-size: x-small;"><br /></span></p><p><span style="font-size: x-small;">$RefreshDSURL = 'groups/' + $WSIDAdmin + '/datasets/' + $DSIDRefresh + '/refreshes'</span></p><p><span style="font-size: x-small;">$MailFailureNotify = @{"notifyOption"="MailOnFailure"}</span></p><p><span style="font-size: x-small;">Invoke-PowerBIRestMethod -Url $RefreshDSURL -Method Post -Body $MailFailureNotify</span></p><p><span style="background-color: white; color: #171717; font-size: large; text-align: justify;">Please note after refresh done from PowerShell, if you look at the refresh history, you will see the refresh </span><span style="background-color: white; text-align: justify;"><span style="color: #171717; font-size: medium;"> type is empty. It seems like Power BI could not decide if the refresh is done by on demand or Scheduled.</span></span></p><p><span style="background-color: white; text-align: justify;"></span></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQx0kuKVqyMgbEa3xi7lcOa9pZaJCpaFmRMT51y1sDn8p5EkGK4xbWX1dE3MtAZoeTWbYw3djtKyDJKqp7DJCIteqL9Q2u8ddCoiIU-f7lV79G4hyphenhyphenxq4Q4RE2SiuzAtab6oYZNKeRdyv0/s961/p-9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="474" data-original-width="961" height="198" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQx0kuKVqyMgbEa3xi7lcOa9pZaJCpaFmRMT51y1sDn8p5EkGK4xbWX1dE3MtAZoeTWbYw3djtKyDJKqp7DJCIteqL9Q2u8ddCoiIU-f7lV79G4hyphenhyphenxq4Q4RE2SiuzAtab6oYZNKeRdyv0/w400-h198/p-9.png" width="400" /></a></div><br /><span style="color: #171717; font-size: medium;"><br /></span><p></p>Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com12tag:blogger.com,1999:blog-965447820345581793.post-69196608752804663302020-10-23T14:49:00.003-07:002020-10-23T14:52:00.856-07:00PowerShell script to update Power BI Dataset parameters<p style="text-align: justify;"><span face=""Segoe UI", sans-serif" style="background-color: white; color: #171717; font-size: 12pt;">In Power BI dataset or reports, parameters serve as a way to
easily store and manage a value that can be changed after deployed. You could
set up the parameters like database server name and database names inside Power
BI solution and update through Power BI Settings as discussed before.</span></p>
<p class="MsoNormal" style="background: white; text-align: justify;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;">In this blog, we will use <a href="https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updateparametersingroup">PowerShell</a> to update the <a href="https://community.powerbi.com/t5/Developer/Update-parameters-through-Powershell/td-p/1167203">parameters</a>.
Here is the script to update the following two parameters on dataset named “<b>MyReport</b>”
inside workspace named “<b>My Workspace</b>”. <o:p></o:p></span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face=""Segoe UI", sans-serif" style="color: #171717; font-size: small;"># Install-Module -Name MicrosoftPowerBIMgmt</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"># Connect to Power BI As admin</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">$User = "admin@mycompany.com"</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">$PW = "Password"</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">Connect-PowerBIServiceAccount -Credential $myCred</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span style="font-size: x-small;"><br /></span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"># Get Power BI Workspace ID</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">$WSID = Get-PowerBIWorkspace -Scope Organization -Name 'My Workspace'| ForEach {$_.Id}</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"><br /></span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"># Get Power BI Dataset ID</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">$DSIDMyReport = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | Where {$_.Name -eq "MyReport"} | ForEach {$_.Id}</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"><br /></span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"># Construct Url with Dataset ID</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">$urlUpdateParams = "/datasets/" + $DSIDMyReport + "/Default.UpdateParameters"</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span style="font-size: x-small;"><br /></span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"># Update Parameters Body</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">$body = '{</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> "updateDetails": [</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> {</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> "name": "DatabaseName",</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> "newValue": "NewDBNamev"</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> },</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> {</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> "name": "ServeName",</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> "newValue": "myserver.database.windows.net"</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> }</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"> ]</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;">}'</span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face=""Segoe UI", sans-serif" style="color: #171717;"><span style="font-size: x-small;">$content = 'application/json'</span></span></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717; font-size: x-small;"># Update Dataset Parameters</span></p><p class="MsoNormal" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; text-align: justify;"><span face="Segoe UI, sans-serif" style="background-color: #fcff01; color: #171717; font-size: x-small;"><b>Invoke-PowerBIRestMethod -Url $urlUpdateParams -Method Post -Body $body -ContentType $content</b></span></p><p class="MsoNormal" style="background: white; text-align: justify;"></p><p class="MsoNormal" style="background: white; text-align: justify;"><span face="Segoe UI, sans-serif" style="color: #171717;">As enhancement, you could also use service principal as discussed in <a href="https://www.nickyvv.com/2020/06/refresh-a-power-bi-dataset-with-powershell-and-a-service-principal.html">here</a> to facilitate the login.</span></p>Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com1tag:blogger.com,1999:blog-965447820345581793.post-9327854367014791102020-10-23T10:20:00.006-07:002020-10-23T10:37:54.142-07:00How to configure Power BI dataset to connect to different databases in different stage workspaces?<p style="text-align: justify;"><span face=""Segoe UI", sans-serif" style="background-color: white; color: #171717; font-size: 12pt;">When working in Power BI deployment pipeline, different stages
may have different configurations. For example, each stage can have different
databases. DEV workspace Power BI can connect to dev database while PRD
workspace Power BI will connect to PRD database. How could you deploy Power BI
content in different stage workspaces that will connect to different databases
automatically? Here are the steps to configure.</span></p>
<p style="background: white; text-align: justify;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;"><b><i><u>First, make the database serve name and password as <a href="https://community.powerbi.com/t5/Community-Blog/Using-the-Power-BI-Service-Parameters-to-change-connection/ba-p/392016">parameters</a>
inside Power BI.</u></i></b> This will enable to configure them in the next steps.<o:p></o:p></span></p>
<p style="background: white; text-align: justify;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;">You can go to Power BI desktop “Home”->”Transform data”->
”Transform data”->”Manager Parameters”->”New Parameter”. Enter the
parameter as the screenshot below. Please note you could enter a list of values
for one parameter. See <a href="https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-parameters-in-power-bi-desktop-part-4/">reference</a> for details.<o:p></o:p></span></p><p style="background: white; text-align: justify;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguA_n9ZsptVH0BS1zMV63KDMFaWHzj4NKv2zARQmv6Ft05sCDxA_rY6Gfbqnld9T_ft5Um_N_rox4a8FjUkre2jCw1-QLe9oYr8D1MMj72BOiIsdy4Me2ueyFgKnV6n31b5lkglqjPHxg/s1194/P-1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="778" data-original-width="1194" height="261" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguA_n9ZsptVH0BS1zMV63KDMFaWHzj4NKv2zARQmv6Ft05sCDxA_rY6Gfbqnld9T_ft5Um_N_rox4a8FjUkre2jCw1-QLe9oYr8D1MMj72BOiIsdy4Me2ueyFgKnV6n31b5lkglqjPHxg/w400-h261/P-1.png" width="400" /></a></div><br /><p style="background: white;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;"><b><i><u>Second, configure Power BI connection to use the parameters.</u></i></b>
When you select database, select the parameters as below screenshot.<o:p></o:p></span></p><p style="background: white;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw6zqxS_sBmUPq-ZlorfCA3S1QLfjMdWJR5kB5rjPNBhn15U-CfVPSd078pr6xOeNqDpyqQrqWJgbVA8p-0_fm1bsKYsP1OoZ_cIZH_Zp0-84dp-YGdkj0DuRX-WNSa5-lkysS8oRroVo/s698/p-2.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="334" data-original-width="698" height="191" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw6zqxS_sBmUPq-ZlorfCA3S1QLfjMdWJR5kB5rjPNBhn15U-CfVPSd078pr6xOeNqDpyqQrqWJgbVA8p-0_fm1bsKYsP1OoZ_cIZH_Zp0-84dp-YGdkj0DuRX-WNSa5-lkysS8oRroVo/w400-h191/p-2.png" width="400" /></a></div><br /><p></p><p style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;"><b><i><u>Third, configure table to store the parameters and cards to
<a href="https://workingondata.wordpress.com/2018/03/27/show-dataset-parameter-values-in-power-bi-reports/">display the parameters</a>. </u></i></b>Add this page to the report so you could use it to
debug if there is data issue.<o:p></o:p></span></p><p></p><p style="background: white;">
</p><p style="background: white;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;">You can go to Power BI desktop “Home”->”Transform data”->Right
click the Parameter->”Convert To Query”. Then the parameters will be stored
in a table like below.<o:p></o:p></span></p><p style="background: white; text-align: justify;"></p><p style="background: white;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_6gXoq2hBRTzApMxAm-q7iBtO1zAPQhbXU39gL256rCWCBM-H71hvAesX7meSDA3CXEW-ptT07FVz7xrKQTmMcaV4WBdfW2fSj69Sig6xetiC6JmfCC4ZBGWrj1OZ47aEFO2s5JeDNBY/s611/p-3.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="267" data-original-width="611" height="175" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_6gXoq2hBRTzApMxAm-q7iBtO1zAPQhbXU39gL256rCWCBM-H71hvAesX7meSDA3CXEW-ptT07FVz7xrKQTmMcaV4WBdfW2fSj69Sig6xetiC6JmfCC4ZBGWrj1OZ47aEFO2s5JeDNBY/w400-h175/p-3.png" width="400" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIh_qjYZUZjzZXcuf3AO7kiZ8ePFfIxKt5dK6KvgcTKRgR8aEeWcXqYMe_-tpWDDqhQGQDNrq3DRgZRYLo63DCfU110EPriwUnywegM09N5XAJnEXyFtHLpJvMT5F6HOXJZjTLvnwDVEY/s1503/p-4.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="658" data-original-width="1503" height="175" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIh_qjYZUZjzZXcuf3AO7kiZ8ePFfIxKt5dK6KvgcTKRgR8aEeWcXqYMe_-tpWDDqhQGQDNrq3DRgZRYLo63DCfU110EPriwUnywegM09N5XAJnEXyFtHLpJvMT5F6HOXJZjTLvnwDVEY/w400-h175/p-4.png" width="400" /></a></div><span face=""Segoe UI", sans-serif" style="color: #171717; font-size: 12pt;"><p style="background: white;"><span face=""Segoe UI", sans-serif" style="color: #171717; font-size: 12pt;"><br /></span></p><b><i><u>Forth, deploy the Power BI .pbix file to workspace and identify if
the parameter is displayed in Power BI workspace setting.</u></i></b></span><p></p><p></p><p style="background: white;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;">You can go to Power BI workspace->”Datasets + dataflows”->”More
options”->”Settings”->”Parameters”. Verify the parameter values. You could
also change the value but need to refresh the dataset afterword to pick up the right
parameter. See more <a href="https://docs.microsoft.com/en-us/power-query/power-query-query-parameters">here</a>.<o:p></o:p></span></p><p style="background: white;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn53CsjZvKX1msiArS_jGiu8xpc2pYuAFyNp_ROuMaFu6I3EtjTp0TGbtFXXWVr_I6jrMBTnqUV2J5gVv7FAQr-Kti_bBbN-XeAIHsvbsGtcEyP6CmxmE9wKED_3IPvbW2SGHEXfooqJM/s977/p-5.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="615" data-original-width="977" height="251" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn53CsjZvKX1msiArS_jGiu8xpc2pYuAFyNp_ROuMaFu6I3EtjTp0TGbtFXXWVr_I6jrMBTnqUV2J5gVv7FAQr-Kti_bBbN-XeAIHsvbsGtcEyP6CmxmE9wKED_3IPvbW2SGHEXfooqJM/w400-h251/p-5.png" width="400" /></a></div><span face=""Segoe UI", sans-serif" style="color: #171717; font-size: 12pt;">At this point, you already have a configurable database
connection parameters you could change in the workspace. However, this is still
manual process. The next few steps will automate the process.</span><p></p><p style="background: white; text-align: justify;"></p><p style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;"><b><i><u>Fifth, create a pipeline and create different workspaces as
different environments like DEV, TEST, and PRD.</u></i></b> In our example, we created three
environments like below. For details to create pipeline, please refer Microsoft <a href="https://docs.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines-get-started">Power BI Pipeline document</a>.<o:p></o:p></span></p><p style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGXV92SVCOKROK2MtHLpPcWFwXeVAAovCR6JfTKdCzcpdr0jPO7dno_RE1QlKLgXDZ4LGRXeuy8f7SWwThsKz6MzjxDvVOFa7Sj8uXxK2G-1vEJDex8bOoVZDwNq15bGsFGc7gglKYfxA/s1432/p-6.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="521" data-original-width="1432" height="145" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGXV92SVCOKROK2MtHLpPcWFwXeVAAovCR6JfTKdCzcpdr0jPO7dno_RE1QlKLgXDZ4LGRXeuy8f7SWwThsKz6MzjxDvVOFa7Sj8uXxK2G-1vEJDex8bOoVZDwNq15bGsFGc7gglKYfxA/w400-h145/p-6.png" width="400" /></a></div><br /><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;"><br /></span><p></p><p style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;"><b><i><u>Sixth, configure the <a href="https://docs.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines-get-started#step-4---create-dataset-rules">Dataset rule</a> for each environment.</u></i></b> The
parameter will change to the value configured in the rule automatically!<o:p></o:p></span></p><p></p><p style="background: white;"></p><ul style="text-align: left;"><li><span face=""Segoe UI", sans-serif" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #171717; font-size: 12pt;">In the pipeline stage you want to create a
dataset rule for, select </span><strong style="box-sizing: inherit; outline-color: inherit;"><span face=""Segoe UI",sans-serif">Deployment
settings</span></strong>.</li><li><span face=""Segoe UI", sans-serif" style="color: #171717; font-size: 12pt;">From the Deployment settings pane, select the
dataset you want to create a rule for.</span></li><li><span face=""Segoe UI", sans-serif" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #171717; font-size: 12pt;">Select the type of rule you want to create,
expand the list, and then select </span><strong style="box-sizing: inherit; outline-color: inherit;"><span face=""Segoe UI",sans-serif">Add
rule</span></strong>.</li></ul><p></p><p style="background: white;"><o:p></o:p></p><p style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial;">
</p><p style="background: white;"><o:p></o:p></p><p style="background: white; text-align: justify;"></p><p style="background: white;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;"><br /></span></p><p style="background: white;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvATRKplX9CNndOG4JJaZ4t7VddLBtPmtHfBuBwV3eqjMTSqhlknAGyO_NL0xSqt_AE28guuVKsSSsOTvf5B-kWzLE73mIwX4vNvTP4KFGGaklmXWfz2-R5ILz3s_pV8px5sXPiPVj_RU/s1420/p-8.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="814" data-original-width="1420" height="229" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvATRKplX9CNndOG4JJaZ4t7VddLBtPmtHfBuBwV3eqjMTSqhlknAGyO_NL0xSqt_AE28guuVKsSSsOTvf5B-kWzLE73mIwX4vNvTP4KFGGaklmXWfz2-R5ILz3s_pV8px5sXPiPVj_RU/w400-h229/p-8.png" width="400" /></a></div><br /><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;"><br /></span><p></p><p style="background: white; text-align: justify;"><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;">Now, you could configure the parameter to use DEV and deploy to DEV workspace. Then use pipeline to deploy to TEST workspace. Verify the parameter changed to TEST database automatically. The use pipeline to deploy to PRD workspace. The database will automatically point to PRD now!</span></p><p style="background: white; text-align: justify;">Please note pipeline deployment is different from .pbix file. After .pbix file deployed, it will create two components. One for report and another for dataset. In pipeline deployment, you will need to select BOTH report and dataset and then deploy! If you only select the dataset, the report will not be deployed.</p><span face=""Segoe UI",sans-serif" style="color: #171717; font-size: 12pt;"></span><p></p>Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-5225684672312342802020-09-28T11:14:00.003-07:002020-09-28T11:14:29.003-07:00Power BI new column based on other table<p> We have a Power BI report need to get the total "Approver Tasks" for one submitted "PubsRecord". There are few options and the best way is to calculate on the database view if possible. If you do not have option to modify database, you could use Power BI calculation. </p><p>If you need to have multiple filter and conditions, certain format need to be implemented. Here are two proposals and one will have error.</p><p>This one is correct one.</p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">Number Approval Tasks = <o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">IF(<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
CALCULATE ( <o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> COUNTROWS ( DimApprovalTasks ),<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt; margin-left: .5in;"><span style="font-family: Consolas; font-size: 9pt;"> DimApprovalTasks[LatestTask]= "Yes",<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey]
= FACTPubsRecord[PubsKey] )<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
)= BLANK(),<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
0,<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
CALCULATE ( <o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> COUNTROWS ( DimApprovalTasks ),<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> DimApprovalTasks[LatestTask]=
"Yes",<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> FILTER ( DimApprovalTasks,
DimApprovalTasks[PubsKey] = FACTPubsRecord[PubsKey] )<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
)<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">)<o:p></o:p></span></p><p>
</p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> </span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">This one will have error.</span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">Number Approval Tasks = <o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">IF(<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
CALCULATE ( <o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> COUNTROWS ( DimApprovalTasks ), <o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey]
= FACTPubsRecord[PubsKey] ),<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> FILTER ( DimApprovalTasks, DimApprovalTasks[LatestTask]=
"Yes"),<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
)= BLANK(),<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
0,<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
CALCULATE ( <o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> COUNTROWS ( DimApprovalTasks ),<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey]
= FACTPubsRecord[PubsKey] ),<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;"> FILTER ( DimApprovalTasks, DimApprovalTasks[LatestTask]=
"Yes"),<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">
)<o:p></o:p></span></p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;">
</p><p class="MsoNormal" style="background: #FFFFFE; line-height: 13.5pt;"><span style="font-family: Consolas; font-size: 9pt;">)<o:p></o:p></span></p><p>You will notice the combination of "Filter" and other conditions will give you the flexible way to get calculation against any other tables. </p><p><br /></p>Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-51885402946968513792020-09-17T10:29:00.003-07:002020-09-17T10:29:53.126-07:00Power BI Dataset Refresh Error for Column in Table contains a duplicate value<p>We have a Power BI dataset and report running for few month without refresh error. However it shows the following error and refresh stops.</p><p>Column 'PubsID' in Table 'FACTPubsRecord' contains a duplicate value '1115' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.</p><p><br /></p><p>After debugging the database tables, table relationships, columns, and measures, we were not able to resolve the issue. I started to rebuild the dataset and everything looks good. At the end when I document the dataset and set up the properties of the table, this is one "Key column" identified that is cuasing the problem. The column named PubsId that was unique before. However, it is no longer unique.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPWaNaveG0RU5O8UClBuSyr-sLbQU48tt3Hp-0kMlLL-C_2ELsPB2JEc8kN_ivD-_roe7tl986dCW-Z_UKFqTwDOrRgjAarOQpoeUg_LM47P4-6tFraeLTKJ3xGdk9kGf4Wa_jjqVkeVA/s1352/Refresh-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="772" data-original-width="1352" height="229" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPWaNaveG0RU5O8UClBuSyr-sLbQU48tt3Hp-0kMlLL-C_2ELsPB2JEc8kN_ivD-_roe7tl986dCW-Z_UKFqTwDOrRgjAarOQpoeUg_LM47P4-6tFraeLTKJ3xGdk9kGf4Wa_jjqVkeVA/w400-h229/Refresh-1.png" width="400" /></a></div><br /><p>The Key Column in Power BI properties MUST be unique in order for Power BI refresh to work. The solution is to change it to PubsKey unique value to resolve the issue.</p><p>This is hard lesson that took me two days to finally resolve the isuse.</p><p><br /></p>Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com1tag:blogger.com,1999:blog-965447820345581793.post-22976467027600695732020-06-30T09:49:00.005-07:002020-08-06T14:30:51.511-07:00How to Sort by Month Year Name in Power BI reportWhen you create Power BI report based on Month and Year calendar, the default sorting i s based on the alphabetic name like then screenshot below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRo7pvwcJL1HdsEaepoISBOAOn7JIcXjVRdtxxTRnPLtVM9JYzcO5_qFCbM17n5DO4J8m3foecUrinJkE2IqdqeAfmo1hyphenhyphenEOBm0mjvQ70XAaAwHtzj-oWV3xatHTyJ9hxhy2VrCLQ0nJM/s1600/s1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="194" data-original-width="512" height="151" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRo7pvwcJL1HdsEaepoISBOAOn7JIcXjVRdtxxTRnPLtVM9JYzcO5_qFCbM17n5DO4J8m3foecUrinJkE2IqdqeAfmo1hyphenhyphenEOBm0mjvQ70XAaAwHtzj-oWV3xatHTyJ9hxhy2VrCLQ0nJM/s400/s1.png" width="400" /></a></div>
<br />
If we need to create a report that will display the month and year combination and sorted by the time, you will need to create a different column and apply the sorting. Here are the steps.<br />
<br />
<br />
First you will need to crate a <a href="https://www.oreilly.com/library/view/microsoft-power-bi/9781788290142/996598ea-2c7b-4390-9975-40f17227094c.xhtml">new column</a> like below:<br />
<span style="font-family: consolas; font-size: 9pt;">MonthYearOrder = CONCATENATE(MyCalendar[Year],right(CONCATENATE("00",MyCalendar[MonthNumer]),2))</span><br />
<span style="font-family: consolas; font-size: 9pt;"><br /></span>
Then click the column you need to display like "MMM_YYYY" and select "Sort by column". Pick the new column "MonthYearOrder" just created.<br />
<br />
In the report, you will need to pick either acceding or descending to display the sequence correctly.<br />
<br />
You could use this method to apply the sorting on other columns like "Department" based on the different order you prefer to display.Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com9tag:blogger.com,1999:blog-965447820345581793.post-16582987399016355812020-06-30T09:32:00.001-07:002020-06-30T09:32:27.974-07:00Power BI Streaming Dashboard exampleThere are many different ways to present real data report and dashboard in Power BI. One of the way is to use PubNub that could make your data streams available on the Microsoft Power BI platform. Here is the detailed steps to use <a href="https://www.pubnub.com/blog/microsoft-power-bi-streaming-business-data-to-dashboards/">PubNub for Power BI</a>.<br />
<br />
The first step is to create a Power BI dataset from Power BI service.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiH1I-d1hm8BnTwdSQwGXXsBPvded8w1suCeaE5XGfGgd2BF0rG3nHEayHH83TllwXO8fBNyLCds59rQSTXeadCioQ3wbu4N_FmYFxcJHaHMd7awCIak8uloZgEGiodfIfqR35S0hHqkJA/s1600/p1t.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="607" data-original-width="1274" height="190" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiH1I-d1hm8BnTwdSQwGXXsBPvded8w1suCeaE5XGfGgd2BF0rG3nHEayHH83TllwXO8fBNyLCds59rQSTXeadCioQ3wbu4N_FmYFxcJHaHMd7awCIak8uloZgEGiodfIfqR35S0hHqkJA/s400/p1t.png" width="400" /></a></div>
<br />
The second step is to create the dataset by connecting to PnbNub. The data example is on the PnbNub <a href="https://www.pubnub.com/developers/realtime-data-streams/sensor-network/">site</a>. You need both Channel and Subscription Key to connect.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2RA71IqH0BmLfptdBj5YtlZ583IPBWlwA_F7XEKs6GwI_HRHdNnTwBEMjfJ9gIG3EWXd_Ct8uwjf1SHqqe39lv3-_FOo2DehN9c3z4feSdlJGc5V0kCPy7ytapLPDi7x-6cOJaXR96MQ/s1600/p3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="724" data-original-width="650" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2RA71IqH0BmLfptdBj5YtlZ583IPBWlwA_F7XEKs6GwI_HRHdNnTwBEMjfJ9gIG3EWXd_Ct8uwjf1SHqqe39lv3-_FOo2DehN9c3z4feSdlJGc5V0kCPy7ytapLPDi7x-6cOJaXR96MQ/s400/p3.png" width="358" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFPhZpE6MBb8cS7fV7_SjydSUcPnvW4H719cogaHmklOh27OvMRekTynzzjo4OOK0xMnrOeJiPhnDToX-GftB7IJVfd-8Qra4JtJNab7jnPlfUTDp1i8cPO7VPEy3TouziGAEhlhTvBns/s1600/p2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="741" data-original-width="655" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFPhZpE6MBb8cS7fV7_SjydSUcPnvW4H719cogaHmklOh27OvMRekTynzzjo4OOK0xMnrOeJiPhnDToX-GftB7IJVfd-8Qra4JtJNab7jnPlfUTDp1i8cPO7VPEy3TouziGAEhlhTvBns/s400/p2.png" width="352" /></a></div>
<br />
The third step is to create the dashboard from Power BI service. You should select the "Streaming dataset".<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHOBF6-GT-AWRK7-dKq5scQZxqJs_BUvRYPu1q2THHWEtR-DESU9g3GLcVxbWIP2jrWcVlNtHe2uSAKnqm2bo-NuXi0JCsD8-9m1qHO77xI1Nr9ZLNdEkpKkp8TMmcaSTPEwPTi_zjgSk/s1600/p4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="613" data-original-width="400" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHOBF6-GT-AWRK7-dKq5scQZxqJs_BUvRYPu1q2THHWEtR-DESU9g3GLcVxbWIP2jrWcVlNtHe2uSAKnqm2bo-NuXi0JCsD8-9m1qHO77xI1Nr9ZLNdEkpKkp8TMmcaSTPEwPTi_zjgSk/s400/p4.png" width="260" /></a></div>
<br />
<br />
The forth step is to add tiles to the report as in the below screenshots.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisdhp05mLSLbd28xRnW6n_vCBC1H-_qH33nXVUvnbpW2O-2FkJtw_sFvzxp7RYij4RtWO786WDJICVkg2_g1xCHoOsNd3KsJIzaTMgAxFoKpS2SY1aRuZwKFIhqlRxT-MibV6dTAeq_zU/s1600/p5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="662" data-original-width="1564" height="168" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisdhp05mLSLbd28xRnW6n_vCBC1H-_qH33nXVUvnbpW2O-2FkJtw_sFvzxp7RYij4RtWO786WDJICVkg2_g1xCHoOsNd3KsJIzaTMgAxFoKpS2SY1aRuZwKFIhqlRxT-MibV6dTAeq_zU/s400/p5.png" width="400" /></a></div>
<br />
There are some gotchas you should be aware of the Power BI streaming. Please read <a href="https://bpcs.com/blog/power-bi-streaming-data-sets-the-good-the-great-and-the-gotchas">Microsoft blog</a> for details.<br />
<br />
There are few different ways for Power BI streaming, you could also use <a href="https://powerbi.microsoft.com/en-us/blog/push-rows-to-a-power-bi-streaming-dataset-without-writing-any-code-using-microsoft-flow/%C2%A0%C2%A0">Flow automation</a> to push data to Power BI dataset.Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com3tag:blogger.com,1999:blog-965447820345581793.post-10916286252871087102020-06-24T21:09:00.002-07:002020-06-24T21:09:31.868-07:00Create summary table and ensure no empty value for Power BIIf you need to create a summary table for Power BI report, you should be careful not to return empty value. Here is one example.<br />
<br />
NoActionTakenApprovalTasks = SUMMARIZE(DimApprovalTasks,<br />
<span style="white-space: pre;"> </span> DimApprovalTasks[Approver],<br />
<span style="white-space: pre;"> </span> DimApprovalTasks[ApprovalRole],<br />
<span style="white-space: pre;"> </span> "Total Tasks", countrows(DimApprovalTasks),<br />
<span style="white-space: pre;"> </span> "Total No Action Taken Tasks", IF(<br />
<span style="white-space: pre;"> </span>CALCULATE(COUNTROWS(DimApprovalTasks), DimApprovalTasks[Status]= "No Action Taken")= BLANK(),<br />
<span style="white-space: pre;"> </span>0,<br />
<span style="white-space: pre;"> </span>CALCULATE(COUNTROWS(DimApprovalTasks), DimApprovalTasks[Status]= "No Action Taken")<br />
<span style="white-space: pre;"> </span>)<br />
)<br />
<br />
<br />
In this example, we add an IF condition to return either 0 or value if not empty. This will be same to create measure to ensure value set to 0 when empty value returned. An example is below.<br />
<br />
<div style="background-color: #fffffe; font-family: Consolas; font-size: 12px; line-height: 18px; white-space: pre;">
<div>
Total Approved Tasks = </div>
<div>
IF(</div>
<div>
CALCULATE ( </div>
<div>
COUNTROWS ( DimApprovalTasks ),</div>
<div>
DimApprovalTasks[Status]= "Approved"</div>
<div>
) = BLANK(),</div>
<div>
0,</div>
<div>
CALCULATE ( </div>
<div>
COUNTROWS ( DimApprovalTasks ),</div>
<div>
DimApprovalTasks[Status]= "Approved"</div>
<div>
)</div>
<div>
)</div>
</div>
Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-49341862742976771472020-06-24T21:02:00.002-07:002020-06-24T21:02:40.247-07:00Power bi calculated column multiple if statements exampleWe have a requirement to display the percentage distribution in Power BI. The percentage can be any value from 0% to 100%. However, the report should show the percentage range like 10-20, 20-30, etc.<br />
<br />
In order to do this, a calculated column with multiple if statements will be required. If you know the syntax, it will be quite simple. Here is the example.<br />
<br />
<br />
Percentage Range =<br />
IF([No Action Taken %]=0.00,"0",<br />
IF(AND([No Action Taken %]>0.00, [No Action Taken %]<=0.10),"1-10",<br />
IF(AND([No Action Taken %]>0.10, [No Action Taken %]<=0.20),"11-20",<br />
IF(AND([No Action Taken %]>0.20, [No Action Taken %]<=0.30),"21-30",<br />
IF(AND([No Action Taken %]>0.30, [No Action Taken %]<=0.40),"31-40",<br />
IF(AND([No Action Taken %]>0.40, [No Action Taken %]<=0.50),"41-50",<br />
IF(AND([No Action Taken %]>0.50, [No Action Taken %]<=0.60),"51-60",<br />
<span style="white-space: pre;"> </span> IF(AND([No Action Taken %]>0.60, [No Action Taken %]<=0.70),"61-70",<br />
<span style="white-space: pre;"> </span> IF(AND([No Action Taken %]>0.70, [No Action Taken %]<=0.80),"71-80",<br />
<span style="white-space: pre;"> </span> IF(AND([No Action Taken %]>0.80, [No Action Taken %]<=0.90),"81-90",<br />
<span style="white-space: pre;"> </span>IF(AND([No Action Taken %]>0.90, [No Action Taken %]<1),"91-99", "100"<br />
<br />
)))))))))))<br />
<br />
There is another option to use <a href="https://community.powerbi.com/t5/Desktop/Calculated-Column-with-multiple-IF-Else-Then/td-p/321463">switch</a> you could also try.<br />
<br />
<br />
<br />
<br />
<br />Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-86975417312163456822020-06-18T11:31:00.004-07:002020-06-18T11:35:18.806-07:00Populate user office location for Power BIThere is a need to populate user office location for Power BI. Here is one of the quickest way to get this information trough <a href="https://docs.microsoft.com/en-us/powershell/module/addsadministration/get-aduser?view=win10-ps">AD Powershell</a>.<br />
<br />
I've quickly developed a script to have user office populate in a csv file and upload to SharePoint for Power BI to consume. Here is the script based on <a href="https://www.adamfowlerit.com/2016/07/null-not-null-powershell/ ">AD Powershell</a>.<br />
<br />
<br />
$ADUsers = Get-ADUser -Filter * -Properties physicalDeliveryOfficeName | where physicalDeliveryOfficeName -ne $null<br />
$outputFile = "C:\Harry\Projects\SPCOE\Scripts\GetADUsers\Output\ADUsers.csv"<br />
<br />
$rows = @()<br />
<br />
foreach($ADUser in $ADUsers)<br />
{<br />
$rows += New-Object -TypeName PSObject -Property @{ <br />
<br />
Name = $ADUser.Name<br />
UPN = $ADUser.UserPrincipalName <br />
Office = $ADUser.physicalDeliveryOfficeName <br />
<br />
} | Select-Object Name,UPN,Office<br />
<br />
}<br />
<br />
$rows | Export-Csv $outputFile -NoTypeInformation -Force -ErrorAction SilentlyContinueHarry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-15000912699613384042020-06-15T09:14:00.000-07:002020-06-15T09:14:45.691-07:00Power BI for LMS365 learning data<span style="background: white; color: #333333; font-family: inherit; text-align: justify;">In order to get learning data from
cloud LMS365, you could use either </span><span style="background: white; color: #171717; font-family: inherit; text-align: justify;"><a href="https://docs.microsoft.com/en-us/connectors/lms365/#creating-a-connection">connector</a>
for Microsoft products like <strong>Logic Apps, Power Automate, and Power Apps or </strong></span><em style="font-family: inherit; text-align: justify;"><span style="background: white; color: #333333;"><a href="https://helpcenter.elearningforce.com/hc/en-us/articles/211362469-How-to-create-Power-BI-report-with-LMS365-cloud-data-using-the-OData-service-v4">OData web services</a> directly. For </span></em><b style="font-family: inherit; text-align: justify;"><span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><a href="https://powerbi.microsoft.com/en-us/"><span style="background: white; color: black;">Power BI</span></a><span style="background: white; color: black;"><span style="float: none;">,</span></span></span></b><span style="background: white; color: black; font-family: inherit; text-align: justify;"> </span><span style="background: white; color: #333333; font-family: inherit; text-align: justify;">you
could use <em>OData</em>
with API Secret Key for your tenant. The steps have been described in details
by Microsoft. There are few tips you should be aware of.</span><br />
<div class="MsoNormal" style="text-align: justify;">
</div>
<ol>
<li><span style="font-family: inherit;">You
need to get the </span><span style="background: white; color: #333333; font-family: inherit;">API Secret
Key for your tenant from the support before you can use the API.</span></li>
<li><span style="font-family: inherit;">The
user name normally is “api”</span></li>
<li><span style="font-family: inherit;">After
connected to </span><span style="background: white; color: #333333; font-family: inherit;">LMS365 through
OData, you should not import all tables. You will get an error when you
importing all tables.</span></li>
<li><span style="font-family: inherit;">Normally
you only need the following enties.</span></li>
</ol>
<br />
<div class="MsoNormal" style="text-align: justify;">
</div>
<ul>
<li><span style="font-family: inherit;">Course
Catalog</span></li>
<li><span style="font-family: inherit;">Course</span></li>
<li><span style="font-family: inherit;">User</span></li>
<li><span style="font-family: inherit;">Enrollment</span></li>
<li><span style="font-family: inherit;">Assignment</span></li>
<li><span style="font-family: inherit;">SCORM</span></li>
<li><span style="font-family: inherit;">SCORM
Statuses</span></li>
<li><span style="font-family: inherit;">Quiz
User Statuses</span></li>
<li><span style="font-family: inherit;">Learning
Module User Statuses</span></li>
</ul>
You
should set up the refresh schedule using the same OData credential. The example of the report is below.<br />
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnnHrOS64e3ZIVSj5xZdrc0OscKJGkM1o6T9DoCj5CGtO1zdLOXS-LLTqwe_CfPrBd2IzJLsFoH1hUd5w-Kq-b6ZmKp8ISq2BT-x_NNac5SWAOu0tLTVQibVwQrVLDEq7CNpaGsIetyWw/s1600/Untitled.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="686" data-original-width="1222" height="223" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnnHrOS64e3ZIVSj5xZdrc0OscKJGkM1o6T9DoCj5CGtO1zdLOXS-LLTqwe_CfPrBd2IzJLsFoH1hUd5w-Kq-b6ZmKp8ISq2BT-x_NNac5SWAOu0tLTVQibVwQrVLDEq7CNpaGsIetyWw/s400/Untitled.png" width="400" /></a></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-72716717719636481602020-06-11T11:36:00.002-07:002020-06-11T11:36:35.407-07:00How to invoke Azure function inside PowerShell or Azure Runbook<div>
The way PowerShell to call Azure function is same as <a href="https://www.sharepointdiary.com/2018/04/call-sharepoint-online-rest-api-from-powershell.html">call rest web service</a>. There are few tips as below.</div>
<div>
<ol>
<li>Use client object module and add the module into PowerShell or Azure Runbook</li>
<li>Azure function might have security configured and you will need to add "Access-Control-Allow-Origin"</li>
<li>You might need to pause the result but in our case it is simple "Yes" or "No"</li>
</ol>
<div>
The example below can give you quick start.</div>
</div>
<div>
<br /></div>
<div>
<div style="background-color: #fffffe; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<div>
<span style="color: green;"># Add references for Runbook</span></div>
<div>
Add-Type -Path <span style="color: #a31515;">"C:\Modules\User\Microsoft.SharePoint.Client\Microsoft.SharePoint.Client.dll"</span></div>
<div>
Add-Type -Path <span style="color: #a31515;">"C:\Modules\User\Microsoft.SharePoint.Client\Microsoft.SharePoint.Client.Runtime.dll"</span></div>
<div>
<br /></div>
<div>
<span style="color: #001188;">$spoSiteUrl</span> =<span style="color: #a31515;">"https://yourcompany.sharepoint.com/sites/pubs"</span></div>
<div>
<span style="color: #001188;">$creds</span> = Get-AutomationPSCredential -Name <span style="color: #a31515;">'O365_svc'</span></div>
<div>
<br /></div>
<div>
<span style="color: green;"># Load SharePoint online CSOM for email</span></div>
<div>
<span style="color: #001188;">$ctx</span> = New-Object Microsoft.SharePoint.Client.ClientContext(<span style="color: #001188;">$spoSiteUrl</span>)</div>
<div>
<span style="color: #001188;">$userName</span> = <span style="color: #001188;">$creds</span>.UserName</div>
<div>
<span style="color: #001188;">$password</span> = <span style="color: #001188;">$creds</span>.Password</div>
<div>
<span style="color: #001188;">$credentials</span> = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials(<span style="color: #001188;">$userName</span>, <span style="color: #001188;">$password</span>)</div>
<div>
<span style="color: #001188;">$ctx</span>.Credentials = <span style="color: #001188;">$credentials</span></div>
<div>
<span style="color: #001188;">$ctx</span>.AuthenticationMode = [Microsoft.SharePoint.Client.ClientAuthenticationMode]::Default</div>
<div style="background-color: #fffffe; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<br /></div>
$<span style="color: #001188;">PubsId</span>=12345<br /><div>
<span style="color: #001188;">$RequestUrl</span>=<span style="color: #a31515;">'https://yurcompany.azurewebsites.net/api/PublicationLookup?code=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==&qry=canbepartiallyapproved&id='</span>+<span style="color: #001188;">$PubsId</span></div>
<div>
write-Output <span style="color: #001188;">$RequestUrl</span></div>
<div>
<span style="color: #001188;">$WebSession</span> = New-Object Microsoft.PowerShell.Commands.WebRequestSession</div>
<div>
<span style="color: #001188;">$WebSession</span>.Credentials = <span style="color: #001188;">$Context</span>.Credentials</div>
<div>
<span style="color: #001188;">$WebSession</span>.Headers.Add(<span style="color: #a31515;">"Accept"</span>, <span style="color: #a31515;">"application/json;odata=verbose"</span>)</div>
<div>
<span style="color: #001188;">$WebSession</span>.Headers.Add(<span style="color: #a31515;">"Access-Control-Allow-Origin"</span>, <span style="color: #a31515;">"mycompany.sharepoint.com"</span>)</div>
<div>
</div>
<div>
<span style="color: #001188;">$Result</span>=<span style="color: #a31515;">"No"</span></div>
<div>
<span style="color: green;">#Invoke Rest Method</span></div>
<div>
<span style="color: #001188;">$Result</span> = Invoke-RestMethod -Method Get -WebSession <span style="color: #001188;">$WebSession</span> -Uri <span style="color: #001188;">$RequestURL</span></div>
<div>
write-Output <span style="color: #001188;">$Result</span> <span style="color: #001188;">$pubs</span>.Id </div>
<div>
<br /></div>
</div>
</div>
Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-61519692654775444542020-06-11T11:23:00.001-07:002020-06-11T11:37:07.266-07:00Tips to filter Power BI report using query string parameters in the URL<br />
<div class="MsoNormal" style="text-align: justify;">
With increasing usage of Power BI report, there is a case
that users request to have a link on the SharePoint list to query all related information
on the item. One example is as below.<o:p></o:p></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
One event list with all different events<o:p></o:p></div>
<div class="MsoNormal" style="text-align: justify;">
Another publication list that have look up column linked to
the event<o:p></o:p></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
The requirement is to have a link to the event list item
that will display ALL the publications related to that event. The solution is
to <a href="https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters">Filter a Power BI report</a> using query string parameters in the URL as the screenshot below.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheeGwEIQPXIDelAPXh9Ku24xzRVoDm_Lq4bOsrTBkEqbCp6sG-PHVMBUe5b74cz4oqq1i57qlBq9mVyJY7PLkDljBwO27gmi4rEI67XQ9EtdWr2wJsUDz3SjRKXpHGm7Vp1sGjTsI6yfw/s1600/RP-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="620" data-original-width="1384" height="178" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheeGwEIQPXIDelAPXh9Ku24xzRVoDm_Lq4bOsrTBkEqbCp6sG-PHVMBUe5b74cz4oqq1i57qlBq9mVyJY7PLkDljBwO27gmi4rEI67XQ9EtdWr2wJsUDz3SjRKXpHGm7Vp1sGjTsI6yfw/s400/RP-1.png" width="400" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Here are some tricks and tips.<o:p></o:p></div>
<br />
<div class="MsoNormal">
</div>
<ol>
<li>Filter
query string parameters only works on <span style="background: white; color: #171717;"> <b>Power BI service</b> or <b>apps</b>. It does not seem to work
on the URL embedded in SharePoint Online or WebSite.</span></li>
<li><span style="background-color: white; color: #171717;">The query can work for </span><b style="color: #171717;">hidden page</b><span style="background-color: white; color: #171717;">, </span><b style="color: #171717;">hidden
fields</b></li>
<li><b style="color: #171717;">Table</b><span style="background-color: white; color: #171717;"> and </span><b style="color: #171717;">Field</b><span style="background-color: white; color: #171717;"> names
are case-sensitive, </span><b style="color: #171717;">value</b><span style="background-color: white; color: #171717;"> isn't.</span></li>
<li>The query syntax is different for data type like Number or String. You need to add '' to the string value</li>
<li>The
last part in report URL like ‘?noSignUpCheck=1’ need to be removed before
adding the filter</li>
<li>You might
need to change the special characters like space to %20</li>
</ol>
<br />
<div class="MsoNormal">
The example is like below.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This one will not work before removing the ‘?noSignUpCheck=1’.</div>
<div class="MsoNormal">
https://app.powerbi.com/groups/9d999dde-1b4e-abcd-abce-xxxxxxxxxxxxxx/reports/01e1eb9e-abcd-4567-b500-xxxxxxxxxxxxxx/ReportSection1c9c48720016d813ded1<span style="background-color: yellow;">?noSignUpCheck=1</span>?<span style="background: white; color: #171717;">filter=<em><span style="font-family: "calibri" , sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">FACTPubsRecord</span></em>/PubsMeetingID%20eq%201549<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<o:p>This will work.</o:p></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
https://app.powerbi.com/groups/9d999dde-1b4e-abcd-abce-xxxxxxxxxxxxxx/reports/01e1eb9e-abcd-4567-b500-xxxxxxxxxxxxxx/ReportSection1c9c48720016d813ded1?<span style="background: white; color: #171717;">filter=<em><span style="font-family: "calibri" , sans-serif; mso-ascii-theme-font: minor-latin; mso-bidi-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">FACTPubsRecord</span></em>/PubsMeetingID%20eq%201549</span><o:p></o:p></div>
<br />
<div class="MsoNormal">
<br /></div>
You could use this URL to send specific query to end users or add the calculated column to SharePoint list.Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-85433308265730581612020-02-21T15:41:00.001-08:002020-02-21T15:41:10.422-08:00How to use REST API call to query SharePoint list over 5000 threshold?We have a SharePoint SPFx project that is using SPFx JS and REST API to query few large SharePoint lists with over 5000 items. We got the following error:<br />
<br />
<span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: red;">Microsoft.SharePoint.SPQueryThrottledException</span></span><br />
<span style="font-family: "Courier New"; font-size: 10pt;"><b><i><span style="color: red;">The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.</span></i></b></span><br />
<div>
<span style="font-family: "Courier New"; font-size: 10pt;"><br /></span></div>
The two calls for SPFx JS and REST call are listed below.<br />
<br />
https://mycompany.sharepoint.com/sites/pubs-stg/_api/web/lists/getbytitle('Journals')/Items?$select=ID&$filter=(ISDNNumber eq '0160-6999')<br />
<br />
let spRecs = await web.lists.getByTitle(listName).items.filter("Publication_x0020_Record_x0020_I eq " + recordId).getAll();<br />
<br />
After some testing, it seems like we could resolved this issue by adding the <a href="https://stackoverflow.com/questions/40362465/sharepoint-online-rest-api-for-list-calling-filters-and-limit-the-return-items">index</a>. We looked at the two calls and both are using filter on the field "Publication_x0020_Record_x0020_I" lookup column to another list. The solution is to add the index to this field!<br />
<br />
After index column "Publication_x0020_Record_x0020_I" , the calls will be successful. You could also add other fields to the filter as long as the first filter returns less than 5000 items. Here is the example:<br />
<br />
https://mycompany.sharepoint.com/sites/pubs-stg/_api/web/lists/getbytitle('Journals')/Items?$select=ID&$filter=(ISDNNumber eq '0160-6999') and (Abbreviation eq 'AADE Ed J')<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<span style="font-family: "Courier New"; font-size: 10pt;"><br /></span>
<br />
<br />Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-91002860981777702172019-12-17T09:25:00.004-08:002019-12-17T09:25:54.123-08:00How to resolve Nintex forms custom button submit AND redirect issueWe have a Nintex forms with one custom button submit then redirect to a confirmation page. When user click the button, it was redirecting to something strange. See screenshot below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7-CXHvczYJhDv0i_wW7gpu5iPcIb8Ps-LCJL3Sm9WYuK4rkoSrs0N1jqizv2b6eG9mH_YKEVX1xMyN5WsIvpC9a5buJ-vL2adUmtIr5sK-oxcPnum5e5YYWn2TMiBy8ObRZM6P7J4XOE/s1600/WF-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="276" data-original-width="1471" height="75" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7-CXHvczYJhDv0i_wW7gpu5iPcIb8Ps-LCJL3Sm9WYuK4rkoSrs0N1jqizv2b6eG9mH_YKEVX1xMyN5WsIvpC9a5buJ-vL2adUmtIr5sK-oxcPnum5e5YYWn2TMiBy8ObRZM6P7J4XOE/s400/WF-1.png" width="400" /></a></div>
After debugging this issue, it seems like Nintex form button redirect could not resolve the link you copied from browser directly. See the screenshot below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOrWPXszcHOaBXld5ScL0wHqLcXsctg9boMSX_iIycioXmEFj6zbFPZju9kRjbevX5PH4GDpX6dQf7-SMF1DZ5DiDC0S3R6-ICnpmmE_L33nxvPlW0RSQtjpMyHzIputh4qiGZeKReR8Q/s1600/WF-2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="733" data-original-width="1530" height="191" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOrWPXszcHOaBXld5ScL0wHqLcXsctg9boMSX_iIycioXmEFj6zbFPZju9kRjbevX5PH4GDpX6dQf7-SMF1DZ5DiDC0S3R6-ICnpmmE_L33nxvPlW0RSQtjpMyHzIputh4qiGZeKReR8Q/s400/WF-2.png" width="400" /></a></div>
<br />
The solution is to copy the redirect URL from browser to notepad first, then copy to the Nintex form. This seems like trivial but took me an hour to figured out from <a href="https://community.nintex.com/t5/Nintex-for-SharePoint/Nintex-forms-custom-button-submit-AND-redirect/td-p/2406">Nintex support site</a>.<br />
<br />
Hope this will remind me in the future.Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com13tag:blogger.com,1999:blog-965447820345581793.post-56710075075272936672019-12-12T14:19:00.002-08:002019-12-12T14:19:45.438-08:00 How to resolve Nintex workflow email action 'The specified string is not in the form required for a subject.' error<br />
<div class="MsoNormal" style="text-align: justify;">
We have a running Nintex workflow in production failed for
few email actions with the error message <b><span style="color: red;">“The specified string is not in the
form required for a subject.”</span></b><o:p></o:p></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8vWS0oc13SV6TZeW_eVaL3qkuJdQMlR9FWDxg44P32o1kX-8-yYW-87544cL2Az344psJQ64Ut4mHGZZcH9_3ExHHghkQhVychMI30KQLLQ2rt72YehnG8pisnR-RN0pJ2a_reGmAuAk/s1600/NintexTitleError.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="305" data-original-width="1194" height="101" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8vWS0oc13SV6TZeW_eVaL3qkuJdQMlR9FWDxg44P32o1kX-8-yYW-87544cL2Az344psJQ64Ut4mHGZZcH9_3ExHHghkQhVychMI30KQLLQ2rt72YehnG8pisnR-RN0pJ2a_reGmAuAk/s400/NintexTitleError.png" width="400" /></a></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
<o:p> </o:p>After debugging this, it seems like there is an limitation for
O365 outlook that <a href="https://www.experts-exchange.com/questions/24215532/How-long-can-a-subject-line-be-in-Outlook-Is-there-a-way-to-change-the-limit-exch-to-avoid-truncation.html">subject line</a> cannot be folded across multiple lines. It does
not seem to have the character limitation for the subject. However, no matter
how long of a subject you submit, exchange will ultimately <a href="https://blogs.technet.microsoft.com/stuartp/2009/02/17/subjects-appearing-garbled-or-corrupted-when-they-are-encoded/">truncate</a> the
unencoded version of that subject down to 255 characters and append
"...".</div>
<div class="MsoNormal" style="text-align: justify;">
<o:p></o:p></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
So in order to resolve the error listed above, we have implemented
in two different palaces for our SharePoint framework solution as UI with Nintex
workflow.<o:p></o:p></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
1. Inside SharePoint framework solution, trim and leading and
trailing spaces and returns. Then also replace inside returns with space.<o:p></o:p></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div style="background-color: #1e1e1e; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<div style="color: #d4d4d4;">
<br /></div>
<div style="color: #d4d4d4;">
<span style="color: #569cd6;">let</span> <span style="color: #9cdcfe;">trimedTitle</span>: <span style="color: #4ec9b0;">string</span> = <span style="color: #9cdcfe;">title</span>;</div>
<div>
<span style="color: #d4d4d4;"> </span><span style="color: #c586c0;">if</span><span style="color: #d4d4d4;">(</span><span style="color: #9cdcfe;">title</span><span style="color: #d4d4d4;"> !=</span><span style="color: #569cd6;">null</span><span style="color: #d4d4d4;">){</span></div>
<div>
<span style="color: #6a9955;"> //This javascript trim leading and trailing spaces and returns</span></div>
<div style="color: #d4d4d4;">
<span style="color: #9cdcfe;">trimedTitle</span> = <span style="color: #9cdcfe;">title</span>.<span style="color: #dcdcaa;">trim</span>(); </div>
<div style="color: #d4d4d4;">
<br /></div>
<div style="color: #d4d4d4;">
<span style="color: #c586c0;">if</span> (<span style="color: #9cdcfe;">trimedTitle</span> != <span style="color: #569cd6;">null</span>){</div>
<div style="color: #d4d4d4;">
<span style="color: #6a9955;">//This javascript replaces all 3 types of line breaks with a space</span> </div>
<div style="color: #d4d4d4;">
<span style="color: #9cdcfe;">trimedTitle</span> = <span style="color: #9cdcfe;">trimedTitle</span>.<span style="color: #dcdcaa;">replace</span>(<span style="color: #d16969;">/</span><span style="color: #ce9178;">(</span><span style="color: #d16969;">\r\n</span><span style="color: #dcdcaa;">|</span><span style="color: #d16969;">\n</span><span style="color: #dcdcaa;">|</span><span style="color: #d16969;">\r</span><span style="color: #ce9178;">)</span><span style="color: #d16969;">/</span><span style="color: #569cd6;">gm</span>,<span style="color: #ce9178;">" "</span>); </div>
<div style="color: #d4d4d4;">
}</div>
<div style="color: #d4d4d4;">
<br /></div>
<div style="color: #d4d4d4;">
<span style="color: #6a9955;">//The trimedTitle will be the good subject that could be used in exchange email</span></div>
<div style="color: #d4d4d4;">
<br /></div>
</div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
2. In Nintex workflow, add a “Trim String” activity on the
subject before send email.<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvXpAjl3-akBk1I317a0ZRQHj-gD1EGwVYU_JnLZopH93Rf8CfthU6nDspao6_fxoogQootzDFnmdZ2W407OAz4tq9fVtumJjuKdxXLNzuEl6VhyBYFuOwk926Dd7LyGvFZXL9pffASC0/s1600/NintexTrimString.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="540" data-original-width="458" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvXpAjl3-akBk1I317a0ZRQHj-gD1EGwVYU_JnLZopH93Rf8CfthU6nDspao6_fxoogQootzDFnmdZ2W407OAz4tq9fVtumJjuKdxXLNzuEl6VhyBYFuOwk926Dd7LyGvFZXL9pffASC0/s400/NintexTrimString.png" width="338" /></a></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
This will ultimately resolve the email action error 'The
specified string is not in the form required for a subject.'.<o:p></o:p></div>
<br />Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-77964915844937933412019-11-08T14:15:00.001-08:002019-11-08T14:15:21.888-08:00Easy to detect device from SharePoint SPFX React applicationWe have a SharePoint framework application need to behavior differently when invoked from mobile device. Here is the quick way to implement this.<br />
<br />
1. Install <a href="https://www.npmjs.com/package/react-device-detect">react-device-detect package</a><br />
<pre style="background: rgb(247, 247, 247); border-radius: 2px; box-sizing: border-box; color: #333333; font-family: monospace, monospace; font-size: 16px; margin-bottom: 24px; overflow-x: auto; padding: 13px 15px;"><code style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; border-radius: 2px; box-sizing: border-box; font-family: "Fira Mono", "Andale Mono", Consolas, monospace; font-size: 1rem; font-variant-ligatures: none; letter-spacing: 0px; line-height: 24px; padding: 0px;">npm install react-device-detect --save</code></pre>
2. Import the package<br />
<pre class="editor editor-colors" style="background: rgb(247, 247, 247); border-radius: 2px; box-sizing: border-box; font-family: monospace, monospace; font-size: 16px; margin-bottom: 24px; overflow-x: auto; padding: 13px 15px;"><div class="line" style="box-sizing: border-box; fill: none; stroke-miterlimit: 10; stroke: rgb(0, 0, 0);">
<span class="source js"><span class="meta import js"><span class="keyword control js" style="color: #222222; font-weight: bold;">import</span> <span class="punctuation definition begin js">{</span></span></span></div>
<div class="line" style="box-sizing: border-box; fill: none; stroke-miterlimit: 10; stroke: rgb(0, 0, 0);">
<span class="source js"><span class="meta import js"> <span class="variable other module js" style="color: teal;">BrowserView</span><span class="meta delimiter object comma js">,</span></span></span></div>
<div class="line" style="box-sizing: border-box; fill: none; stroke-miterlimit: 10; stroke: rgb(0, 0, 0);">
<span class="source js"><span class="meta import js"> <span class="variable other module js" style="color: teal;">MobileView</span><span class="meta delimiter object comma js">,</span></span></span></div>
<div class="line" style="box-sizing: border-box; fill: none; stroke-miterlimit: 10; stroke: rgb(0, 0, 0);">
<span class="source js"><span class="meta import js"> <span class="variable other module js" style="color: teal;">isBrowser</span><span class="meta delimiter object comma js">,</span></span></span></div>
<div class="line" style="box-sizing: border-box; fill: none; stroke-miterlimit: 10; stroke: rgb(0, 0, 0);">
<span class="source js"><span class="meta import js"> <span class="variable other module js" style="color: teal;">isMobile</span></span></span></div>
<div class="line" style="box-sizing: border-box; fill: none; stroke-miterlimit: 10; stroke: rgb(0, 0, 0);">
<span class="source js"><span class="meta import js"><span class="punctuation definition end js">}</span> <span class="keyword control js" style="color: #222222; font-weight: bold;">from</span> <span class="string quoted double js" style="color: #dd1144;"><span class="punctuation definition string begin js">"</span>react-device-detect<span class="punctuation definition string end js">"</span></span></span><span class="punctuation terminator statement js">;</span></span></div>
</pre>
3. Use it inside your application<br />
<br />
<div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<div>
<span style="color: #c586c0;">if</span>(!<span style="color: #9cdcfe;">isSubmitDisabled</span> && !<span style="color: #9cdcfe;">recordIsReadOnly</span> && <span style="color: #9cdcfe;">isMobile</span>){</div>
<br /><div>
<span style="color: #9cdcfe;">submitMenuProps</span> = {</div>
<div>
<span style="color: #9cdcfe;">items:</span> [</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">key:</span> <span style="color: #ce9178;">'saveOnly'</span>,</div>
<div>
<span style="color: #9cdcfe;">name:</span><span style="color: #ce9178;">'Save only'</span>,</div>
<div>
<span style="color: #9cdcfe;">secondaryText:</span> <span style="color: #ce9178;">'Do not submit to approval workflow'</span>,</div>
<div>
<span style="color: #9cdcfe;">text:</span> <span style="color: #ce9178;">'Save as draft'</span>,</div>
<div>
<span style="color: #9cdcfe;">title:</span> <span style="color: #ce9178;">'Save without submitting to a workflow'</span>,</div>
<div>
<span style="color: #9cdcfe;">iconProps:</span> { <span style="color: #9cdcfe;">iconName:</span> <span style="color: #ce9178;">'Save'</span>},</div>
<div>
<span style="color: #9cdcfe;">onClick:</span> <span style="color: #569cd6;">this</span>.<span style="color: #9cdcfe;">props</span>.<span style="color: #9cdcfe;">onSaveOnly</span></div>
<div>
}, </div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">key:</span> <span style="color: #ce9178;">'Submit'</span>,</div>
<div>
<span style="color: #9cdcfe;">name:</span><span style="color: #ce9178;">'Submit'</span>,</div>
<div>
<span style="color: #9cdcfe;">secondaryText:</span> <span style="color: #ce9178;">'Submit to approval workflow'</span>,</div>
<div>
<span style="color: #9cdcfe;">text:</span> <span style="color: #ce9178;">'Submit from Mobile'</span>,</div>
<div>
<span style="color: #9cdcfe;">title:</span> <span style="color: #ce9178;">'Submitting to a workflow'</span>,</div>
<div>
<span style="color: #9cdcfe;">iconProps:</span> { <span style="color: #9cdcfe;">iconName:</span> <span style="color: #ce9178;">'SaveToMobile'</span>},</div>
<div>
<span style="color: #9cdcfe;">onClick:</span> <span style="color: #569cd6;">this</span>.<span style="color: #9cdcfe;">props</span>.<span style="color: #9cdcfe;">onSubmit</span></div>
<div>
} </div>
<div>
]</div>
<div>
};</div>
<div>
}</div>
</div>
Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-41354990955342922852019-10-10T09:21:00.002-07:002019-10-10T09:21:31.627-07:00Procedure to resolve the issue - cannot add SharePoint online list item with form does not exist error<span style="font-family: inherit;">We have found an issue that we could add new item to a SharePoint online list. The error is the form does not found. After debugging the issue, we identified that display item, edit item, and add item all have the same issue. Here is the details how to debug and fix the issue.</span><div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">1. Verify three forms. If you understand how SharePoint works, you will know immediately that this should be the issue on SharePoint three form issue (NewForm.aspx, DidForm.aspx, and EditForm.aspx). You can verify after open the site with SharePoint designer. </span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">2. Try to recreate the missing forms. The next step is to use SharePoint designer to add these three forms as described <a href="https://o365knowledgeshare.wordpress.com/2018/10/16/sharepoint-online-list-error-unable-to-find-the-default-display-form-for-list/">here</a>. However, we got server error.</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">3. The next step is to migrate the same list from another place using Sharegate. The migration failed and has the following message.</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">"Unable to create web part
'Microsoft.SharePoint.WebPartPages.ListFormWebPart': A Web Part or Web Form
Control on this Page cannot be displayed or imported. You don't have Add and
Customize Pages permissions required to perform this action.. In Office 365,
this is mainly caused by the farm setting "Custom Script" being
deactivated."</span></div>
<div>
<div class="MsoNormal">
<span style="font-family: inherit;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">Now we identified the root cause.</span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">4. Enable the "Custom Script" on the site using the script described <a href="https://support-desktop.sharegate.com/hc/en-us/articles/115000643668-Unable-to-create-or-update-WebPart">here</a>. </span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal" style="background: white; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: inherit;"><span style="color: blue; font-size: 9pt;">Connect-SPOService</span><span style="font-size: 9pt;">
<span style="color: navy;">-Url</span> <span style="color: blueviolet;">https://mycompany-admin.sharepoint.com</span><o:p></o:p></span></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal" style="background: white; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: inherit;"><span style="color: blue; font-size: 9pt;">Set-SPOSite</span><span style="font-size: 9pt;">
<span style="color: navy;">-Identity</span> <span style="color: blueviolet;">https://mycompany.sharepoint.com</span>
<span style="color: navy;">-DenyAddAndCustomizePages</span> <span style="color: purple;">0 <o:p></o:p></span></span></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
</div>
<div>
<span style="font-family: inherit;">5. The final step is either recreate the three forms from SharePoint designer or migrate content from another environment.</span></div>
</div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<span style="font-family: inherit;">6. You might want to disable the "Custom Script" again suing Powershell.</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<div class="MsoNormal" style="background: white;">
<span style="font-family: inherit;"><span style="color: blue; font-size: 9pt;">Connect-SPOService</span><span style="font-size: 9pt;"> <span style="color: navy;">-Url</span> <span style="color: blueviolet;">https://mycompany-admin.sharepoint.com</span><o:p></o:p></span></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal" style="background: white;">
<span style="font-family: inherit;"><span style="color: blue; font-size: 9pt;">Set-SPOSite</span><span style="font-size: 9pt;"> <span style="color: navy;">-Identity</span> <span style="color: blueviolet;">https://mycompany.sharepoint.com</span> <span style="color: navy;">-DenyAddAndCustomizePages</span> <span style="color: purple;">1</span></span></span></div>
<div class="MsoNormal" style="background: white;">
<span style="font-size: 9pt;"><span style="color: purple; font-family: inherit;"><br /></span></span></div>
<div class="MsoNormal" style="background: white;">
<span style="font-size: 9pt;"><span style="color: purple; font-family: inherit;"><br /></span></span></div>
</div>
<div>
<span style="font-family: inherit;">We are not sure why the three forms lost in the first place but this blog will give you the procedure to fix the issue. </span></div>
<div>
<br /></div>
Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-11742043571289734012019-09-26T15:20:00.003-07:002019-09-26T15:20:37.298-07:00How to display Url object in reatctableIf you need to display Url object with link and descriptions inside reatctable, here are the steps.<br />
<br />
1. Create a Url interfance and add to the object.<br />
<div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<div>
<span style="color: #c586c0;">export</span> <span style="color: #569cd6;">interface</span> <span style="color: #4ec9b0;">Url</span> {</div>
<div>
<span style="color: #9cdcfe;">Url</span>: <span style="color: #4ec9b0;">string</span>;</div>
<div>
<span style="color: #9cdcfe;">Description</span>: <span style="color: #4ec9b0;">string</span>;</div>
<div>
}</div>
</div>
<br />
<div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<div>
<span style="color: #c586c0;">export</span> <span style="color: #569cd6;">interface</span> <span style="color: #4ec9b0;">ApproverWithLink</span> {</div>
<div>
<span style="color: #9cdcfe;">name</span>?: <span style="color: #4ec9b0;">string</span>;</div>
<div>
<span style="color: #9cdcfe;">email</span>: <span style="color: #4ec9b0;">string</span>;</div>
<div>
<span style="color: #9cdcfe;">id</span>: <span style="color: #4ec9b0;">number</span>;</div>
<div>
<span style="color: #9cdcfe;">role</span>: <span style="color: #4ec9b0;">string</span>;</div>
<div>
<span style="color: #9cdcfe;">status</span>: <span style="color: #4ec9b0;">string</span>;</div>
<div>
<span style="color: #9cdcfe;">lastmodified</span>: <span style="color: #4ec9b0;">string</span>;</div>
<div>
<span style="color: #9cdcfe;">taskLink</span>: <span style="color: #4ec9b0;">Url</span>;</div>
<div>
}</div>
</div>
<br />
<br />
2. Create the object and fill in Url object.<br />
<br />
<div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<div>
<span style="color: #569cd6;">public</span> <span style="color: #569cd6;">static</span> <span style="color: #dcdcaa;">_getApproverWIthUrl</span>(<span style="color: #9cdcfe;">workflowUsers</span>: <span style="color: #4ec9b0;">IWorkflowUsers</span>): <span style="color: #4ec9b0;">ApproverWithLink</span>[] </div>
<br /><div>
<span style="color: #569cd6;">let</span> <span style="color: #9cdcfe;">authors</span>: <span style="color: #4ec9b0;">ApproverWithLink</span>[] = [] <span style="color: #c586c0;">as</span> <span style="color: #4ec9b0;">ApproverWithLink</span>[];</div>
<br /><div>
<span style="color: #c586c0;">if</span>(<span style="color: #9cdcfe;">workflowUsers</span> != <span style="color: #569cd6;">null</span> && <span style="color: #9cdcfe;">workflowUsers</span> != <span style="color: #569cd6;">undefined</span> && </div>
<div>
<span style="color: #9cdcfe;"> workflowUsers</span>.<span style="color: #9cdcfe;">approvers</span> != <span style="color: #569cd6;">null</span> && <span style="color: #9cdcfe;">workflowUsers</span>.<span style="color: #9cdcfe;">approvers</span> != <span style="color: #569cd6;">undefined</span>){</div>
<div>
<span style="color: #569cd6;">let</span> <span style="color: #9cdcfe;">localApprovals</span>: <span style="color: #4ec9b0;">Approver</span>[] = <span style="color: #9cdcfe;">workflowUsers</span>.<span style="color: #9cdcfe;">approvers</span>; </div>
<div>
<span style="color: #c586c0;">for</span> (<span style="color: #569cd6;">let</span> <span style="color: #9cdcfe;">i</span> = <span style="color: #b5cea8;">0</span>; <span style="color: #9cdcfe;">i</span> < <span style="color: #9cdcfe;">localApprovals</span>.<span style="color: #9cdcfe;">length</span>; <span style="color: #9cdcfe;">i</span>++) {</div>
<br /><div>
<span style="color: #569cd6;">let</span> <span style="color: #9cdcfe;">localUrl</span>: <span style="color: #4ec9b0;">Url</span> = {<span style="color: #9cdcfe;">Url:</span> <span style="color: #ce9178;">'https://www.google.com'</span>, <span style="color: #9cdcfe;">Description:</span><span style="color: #ce9178;">'Google'</span>};</div>
<div>
<span style="color: #569cd6;">let</span> <span style="color: #9cdcfe;">returnRec</span>: <span style="color: #4ec9b0;">ApproverWithLink</span> = {<span style="color: #9cdcfe;">name:</span> <span style="color: #9cdcfe;">localApprovals</span>[<span style="color: #9cdcfe;">i</span>].<span style="color: #9cdcfe;">name</span>, </div>
<div>
<span style="color: #9cdcfe;"> email:</span> <span style="color: #9cdcfe;">localApprovals</span>[<span style="color: #9cdcfe;">i</span>].<span style="color: #9cdcfe;">email</span>, <span style="color: #9cdcfe;">id:</span> <span style="color: #b5cea8;">0</span>, <span style="color: #9cdcfe;">role:</span> <span style="color: #9cdcfe;">localApprovals</span>[<span style="color: #9cdcfe;">i</span>].<span style="color: #9cdcfe;">role</span>, </div>
<div>
<span style="color: #9cdcfe;"> status:</span> <span style="color: #9cdcfe;">localApprovals</span>[<span style="color: #9cdcfe;">i</span>].<span style="color: #9cdcfe;">status</span>, </div>
<div>
<span style="color: #9cdcfe;"> lastmodified:</span> <span style="color: #9cdcfe;">localApprovals</span>[<span style="color: #9cdcfe;">i</span>].<span style="color: #9cdcfe;">lastmodified</span>, <span style="color: #9cdcfe;">taskLink:</span> <span style="color: #9cdcfe;">localUrl</span> };</div>
<br /><br /><div>
<span style="color: #9cdcfe;">authors</span>.<span style="color: #dcdcaa;">push</span>(<span style="color: #9cdcfe;">returnRec</span>);</div>
<div>
}</div>
<div>
}</div>
<div>
</div>
<div>
<span style="color: #c586c0;">return</span> <span style="color: #9cdcfe;">authors</span>;</div>
<br /><div>
}</div>
</div>
<br />
<br />
3. Display on the webpart<br />
<br />
<div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<span style="color: #569cd6;">let</span> <span style="color: #9cdcfe;">localApprovals</span>: <span style="color: #4ec9b0;">ApproverWithLink</span>[] = </div>
<div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<span style="color: #9cdcfe;">SPFacade</span>.<span style="color: #dcdcaa;">_getApproverWIthUrl</span>(<span style="color: #569cd6;">this</span>.<span style="color: #9cdcfe;">props</span>.<span style="color: #9cdcfe;">wfApprovalReviewerList</span>);</div>
<br />
<br />
<div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<div>
<span style="color: grey;"><</span><span style="color: #4ec9b0;">ReactTable</span></div>
<div>
<span style="color: #9cdcfe;">data</span>=<span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">localApprovals</span><span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">columns</span>=<span style="color: #569cd6;">{</span>[</div>
<div>
{</div>
<div>
</div>
<div>
<span style="color: #9cdcfe;">columns:</span> [</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Name"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"name"</span></div>
<div>
</div>
<div>
},</div>
<div>
</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Role"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"role"</span></div>
<div>
},</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Status"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"status"</span></div>
<div>
},</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Last Modified"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"lastmodified"</span></div>
<div>
},</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Link"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"taskLink"</span>,</div>
<div>
<span style="color: #dcdcaa;">Cell</span><span style="color: #9cdcfe;">:</span> <span style="color: #9cdcfe;">e</span><span style="color: #569cd6;">=></span><span style="color: grey;"><</span><span style="color: #569cd6;">a</span> <span style="color: #9cdcfe;">href</span>=<span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">e</span>.<span style="color: #9cdcfe;">value</span>.<span style="color: #9cdcfe;">Url</span><span style="color: #569cd6;">}</span><span style="color: grey;">></span> <span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">e</span>.<span style="color: #9cdcfe;">value</span>.<span style="color: #9cdcfe;">Description</span><span style="color: #569cd6;">}</span> <span style="color: grey;"></</span><span style="color: #569cd6;">a</span><span style="color: grey;">></span></div>
<div>
</div>
<div>
}</div>
<div>
]</div>
<div>
}</div>
<div>
]<span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">defaultPageSize</span>=<span style="color: #569cd6;">{</span><span style="color: #b5cea8;">5</span><span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">showPagination</span>=<span style="color: #569cd6;">{false}</span></div>
<div>
<span style="color: #9cdcfe;">noDataText</span>=<span style="color: #ce9178;">"No approvals assigned!"</span></div>
<div>
<span style="color: #9cdcfe;">style</span>=<span style="color: #569cd6;">{</span>{</div>
<div>
<span style="color: #9cdcfe;">height:</span> <span style="color: #ce9178;">"200px"</span> <span style="color: #6a9955;">// This will force the table body to overflow and scroll, since there is not enough room</span></div>
<div>
}<span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">className</span>=<span style="color: #ce9178;">"-striped -highlight"</span></div>
<div>
<span style="color: grey;">/></span></div>
<div>
<span style="color: grey;"></</span><span style="color: #569cd6;">div</span><span style="color: grey;">></span></div>
</div>
<br />
<br />
<br />Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-30289128731048288062019-09-19T09:07:00.006-07:002019-09-19T09:07:59.577-07:00Procedure to hide SharePoint online list public viewsWe have a SharePoint site that is used by two different type of users. One type is admin who should be able to see list item. The other type is regular user who should see some list item based on the column value. We do not want to have item level permissions that will need additional development effort. Here is the quick solution.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjV6BEfEL-SscGwCGXhqkO53Y5af6tLTMO_sK__V20dpFaba8PHgnUEwBdMlTyiBEVG2N_obpXn3WZA-ZmYA0yw91uSNNmQIkvzF5xMqcAOYYfhIlGH295nBwooC0U7XcnzLBvoG2UZH6A/s1600/Untitled.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="436" data-original-width="457" height="381" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjV6BEfEL-SscGwCGXhqkO53Y5af6tLTMO_sK__V20dpFaba8PHgnUEwBdMlTyiBEVG2N_obpXn3WZA-ZmYA0yw91uSNNmQIkvzF5xMqcAOYYfhIlGH295nBwooC0U7XcnzLBvoG2UZH6A/s400/Untitled.png" width="400" /></a></div>
<br />
<br />
<ol>
<li>Create public view to exclude list item based on the column value. </li>
<li>Create another master view to display all items. </li>
<li>Add a page that is only be accessed by Admin group that has the link to the master view and include the master view.</li>
<li>Use Powershell to hide the view from view selections.</li>
</ol>
<br />
<br />
<span style="background-color: #f3f3f3; font-size: x-small;">#Install-Module SharePointPnPPowerShellOnline</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"><br /></span>
<span style="background-color: #f3f3f3; font-size: x-small;">#Change this to the URL of your SharePoint site</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;">$sharePointUrl = "https://yourcompany.sharepoint.com/sites/sitename"</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"><br /></span>
<span style="background-color: #f3f3f3; font-size: x-small;">#Connect-PnPOnline –Url $sharePointUrl –Credentials (Get-Credential)</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"><br /></span>
<span style="background-color: #f3f3f3; font-size: x-small;"># open learner list</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;">$listUrl = "Lists/listname"</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;">$list = Get-PnPList -Identity $listUrl</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"><br /></span>
<span style="background-color: #f3f3f3; font-size: x-small;">$list.Context.Load($list.Views)</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;">$list.Context.ExecuteQuery()</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"><br /></span>
<span style="background-color: #f3f3f3; font-size: x-small;">ForEach($v in $list.Views){</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"><br /></span>
<span style="background-color: #f3f3f3; font-size: x-small;"> if($v.Title -eq 'ListViewName'){ </span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"> $v.Hidden = $true;</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"> #$v.Hidden = $false;</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"> $v.Update()</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"> }</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;">}</span><br />
<span style="background-color: #f3f3f3; font-size: x-small;"><br /></span>
<span style="background-color: #f3f3f3; font-size: x-small;">$list.Context.ExecuteQuery()</span>Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-12890849515437595842019-08-12T14:37:00.004-07:002019-08-12T14:37:49.552-07:00React component to display data in table format with scroll bar and paginationThere are many different ways you could choice to display data in table format with scroll bar and pagination in react. We decided to use <a href="https://www.npmjs.com/package/react-table">react-table</a> since it's simple and has many features.<br />
<br />
<a href="https://www.npmjs.com/package/react-table">React-table</a> might be the easy way and it comes with both scroll bar and page pagination. You could configure the screen size to display and few customization to apply. There are few good <a href="https://codesandbox.io/s/4qrlxjpv9w">examples</a> to show the implementations. We are using this to display the workflow approvals and reviewers for SharePoint list item with <a href="https://github.com/tannerlinsley/react-table/tree/v6#props">pagination hidden</a>. Here is the screenshot.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiy5LVIG8Zr2RGB3_GJWfSZfZG0l6NH2BN11shUYpuMzIWZAWSRabooAF9JizNnUB_a01T8_iAmoWaeJgUw4Dx4sOR_iebRFAc8DwmWKao-oCriBP8veAguir-zpIFy4dJQ4Sd486w3oPQ/s1600/wf-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="287" data-original-width="1379" height="82" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiy5LVIG8Zr2RGB3_GJWfSZfZG0l6NH2BN11shUYpuMzIWZAWSRabooAF9JizNnUB_a01T8_iAmoWaeJgUw4Dx4sOR_iebRFAc8DwmWKao-oCriBP8veAguir-zpIFy4dJQ4Sd486w3oPQ/s400/wf-1.png" width="400" /></a></div>
<br />
The code is also very simple. Here is the sniipet.<br />
<br />
<div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<div>
<span style="color: #c586c0;">return</span> (</div>
<div>
<span style="color: grey;"><</span><span style="color: #569cd6;">div</span> <span style="color: #9cdcfe;">className</span>=<span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">styles</span>.<span style="color: #9cdcfe;">container</span><span style="color: #569cd6;">}</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"><</span><span style="color: #569cd6;">div</span> <span style="color: #9cdcfe;">className</span>=<span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">styles</span>.<span style="color: #9cdcfe;">row</span><span style="color: #569cd6;">}</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"><</span><span style="color: #4ec9b0;">Pivot</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"><</span><span style="color: #4ec9b0;">PivotItem</span> <span style="color: #9cdcfe;">headerText</span>=<span style="color: #ce9178;">"Workflow Approvals"</span> <span style="color: #9cdcfe;">itemIcon</span>=<span style="color: #ce9178;">"People"</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"><</span><span style="color: #569cd6;">div</span> <span style="color: #9cdcfe;">className</span>=<span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">styles</span>.<span style="color: #9cdcfe;">column</span><span style="color: #569cd6;">}</span><span style="color: grey;">></span></div>
<div>
<span style="color: #569cd6;">{</span><span style="color: #6a9955;">/* <h3>Workflow Approvers</h3> */</span><span style="color: #569cd6;">}</span></div>
<div>
<span style="color: grey;"><</span><span style="color: #4ec9b0;">ReactTable</span></div>
<div>
<span style="color: #9cdcfe;">data</span>=<span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">data</span><span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">columns</span>=<span style="color: #569cd6;">{</span>[</div>
<div>
{</div>
<div>
</div>
<div>
<span style="color: #9cdcfe;">columns:</span> [</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Name"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"name"</span>,</div>
<div>
},</div>
<div>
<span style="color: #6a9955;">// {</span></div>
<div>
<span style="color: #6a9955;">// Header: "email",</span></div>
<div>
<span style="color: #6a9955;">// id: "email",</span></div>
<div>
<span style="color: #6a9955;">// accessor: d => d.email</span></div>
<div>
<span style="color: #6a9955;">// },</span></div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Role"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"role"</span></div>
<div>
},</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Status"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"status"</span></div>
<div>
}</div>
<div>
]</div>
<div>
}</div>
<div>
]<span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">defaultPageSize</span>=<span style="color: #569cd6;">{</span><span style="color: #b5cea8;">5</span><span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">showPagination</span>=<span style="color: #569cd6;">{false}</span></div>
<div>
<span style="color: #9cdcfe;">style</span>=<span style="color: #569cd6;">{</span>{</div>
<div>
<span style="color: #9cdcfe;">height:</span> <span style="color: #ce9178;">"200px"</span> <span style="color: #6a9955;">// This will force the table body to overflow and scroll, since there is not enough room</span></div>
<div>
}<span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">className</span>=<span style="color: #ce9178;">"-striped -highlight"</span></div>
<div>
<span style="color: grey;">/></span></div>
<div>
<span style="color: grey;"></</span><span style="color: #569cd6;">div</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"></</span><span style="color: #4ec9b0;">PivotItem</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"><</span><span style="color: #4ec9b0;">PivotItem</span> <span style="color: #9cdcfe;">headerText</span>=<span style="color: #ce9178;">"Workflow Reviewers"</span> <span style="color: #9cdcfe;">itemIcon</span>=<span style="color: #ce9178;">"People"</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"><</span><span style="color: #569cd6;">div</span> <span style="color: #9cdcfe;">className</span>=<span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">styles</span>.<span style="color: #9cdcfe;">column</span><span style="color: #569cd6;">}</span><span style="color: grey;">></span></div>
<div>
<span style="color: #569cd6;">{</span><span style="color: #6a9955;">/* <h3>Workflow Approvers</h3> */</span><span style="color: #569cd6;">}</span></div>
<div>
<span style="color: grey;"><</span><span style="color: #4ec9b0;">ReactTable</span></div>
<div>
<span style="color: #9cdcfe;">data</span>=<span style="color: #569cd6;">{</span><span style="color: #9cdcfe;">data1</span><span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">columns</span>=<span style="color: #569cd6;">{</span>[</div>
<div>
{</div>
<div>
</div>
<div>
<span style="color: #9cdcfe;">columns:</span> [</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Name"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"name"</span>,</div>
<div>
},</div>
<div>
<span style="color: #6a9955;">// {</span></div>
<div>
<span style="color: #6a9955;">// Header: "email",</span></div>
<div>
<span style="color: #6a9955;">// id: "email",</span></div>
<div>
<span style="color: #6a9955;">// accessor: d => d.email</span></div>
<div>
<span style="color: #6a9955;">// },</span></div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Reason"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"reason"</span></div>
<div>
},</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">Header:</span> <span style="color: #ce9178;">"Status"</span>,</div>
<div>
<span style="color: #9cdcfe;">accessor:</span> <span style="color: #ce9178;">"status"</span></div>
<div>
}</div>
<div>
]</div>
<div>
}</div>
<div>
]<span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">defaultPageSize</span>=<span style="color: #569cd6;">{</span><span style="color: #b5cea8;">5</span><span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">showPagination</span>=<span style="color: #569cd6;">{false}</span></div>
<div>
<span style="color: #9cdcfe;">style</span>=<span style="color: #569cd6;">{</span>{</div>
<div>
<span style="color: #9cdcfe;">height:</span> <span style="color: #ce9178;">"200px"</span> <span style="color: #6a9955;">// This will force the table body to overflow and scroll, since there is not enough room</span></div>
<div>
}<span style="color: #569cd6;">}</span></div>
<div>
<span style="color: #9cdcfe;">className</span>=<span style="color: #ce9178;">"-striped -highlight"</span></div>
<div>
<span style="color: grey;">/></span></div>
<div>
<span style="color: grey;"></</span><span style="color: #569cd6;">div</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"></</span><span style="color: #4ec9b0;">PivotItem</span><span style="color: grey;">></span></div>
<br /><div>
<span style="color: grey;"></</span><span style="color: #4ec9b0;">Pivot</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"></</span><span style="color: #569cd6;">div</span><span style="color: grey;">></span></div>
<div>
<span style="color: grey;"></</span><span style="color: #569cd6;">div</span><span style="color: grey;">></span></div>
<div>
</div>
<div>
);</div>
</div>
<br />
<br />
The mock-up data is listed below for your reference.<br />
<br />
<div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Consolas, "Courier New", monospace; font-size: 14px; line-height: 19px; white-space: pre;">
<div>
<span style="color: #c586c0;">export</span> <span style="color: #569cd6;">const</span> <span style="color: #9cdcfe;">data1</span> = [</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">id:</span> <span style="color: #b5cea8;">22</span>,</div>
<div>
<span style="color: #9cdcfe;">name:</span> <span style="color: #ce9178;">'Test User'</span>,</div>
<div>
<span style="color: #9cdcfe;">email:</span> <span style="color: #ce9178;">'test.user@mycompany.com'</span>,</div>
<div>
<span style="color: #9cdcfe;">role:</span> <span style="color: #ce9178;">'Some role'</span>,</div>
<div>
<span style="color: #9cdcfe;">status:</span> <span style="color: #ce9178;">'Open'</span>,</div>
<div>
},</div>
<div>
{</div>
<div>
<span style="color: #9cdcfe;">id:</span> <span style="color: #b5cea8;">23</span>,</div>
<div>
<span style="color: #9cdcfe;">name:</span> <span style="color: #ce9178;">"John Smith"</span>,</div>
<div>
<span style="color: #9cdcfe;">email:</span> <span style="color: #ce9178;">'jsmith@</span><span style="color: #ce9178;">mycompany</span><span style="color: #ce9178;">.com'</span>,</div>
<div>
<span style="color: #9cdcfe;">role:</span> <span style="color: #ce9178;">'Pubs developer'</span>,</div>
<div>
<span style="color: #9cdcfe;">status:</span> <span style="color: #ce9178;">'Open'</span>,</div>
<div>
} </div>
<br /><div>
];</div>
</div>
<br />
There are few other data tables you could check for your own purposes.<br />
<br />
<ol>
<li><a href="https://react.semantic-ui.com/collections/table/#types-pagination">React semantic UI</a></li>
<li><a href="https://material-ui.com/components/tables/">Material UI</a></li>
<li><a href="https://material-table.com/#/docs/features/grouping">Material table with group feature</a></li>
<li><a href="https://mdbootstrap.com/docs/react/tables/scroll/">MD table</a></li>
</ol>
<div>
Some implementations are using bootstrap or other packages that we may not want to introduce to your project.</div>
<br />
<br />
<br />
<br />Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com1tag:blogger.com,1999:blog-965447820345581793.post-54843631870595137022019-07-26T09:10:00.003-07:002019-07-26T09:10:54.402-07:00How to use Microsoft flow to provide Adobe Sign process as http services<div style="text-align: justify;">
We have a external process running on AWS need to integrate with SharePoint document using Adobe Sign. The document will be pushed to SharePoint through Graph API and like to call service to initialize the Adobe Sign process. There are may different ways to provide this as service but the quickest way might be the Microsoft Flow.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
The way I implemented is to start with OoB "Get signatures on a selected file in SharePoint" flow template. Test this manually and then build our own process. The key two actions we need to use are:</div>
<br />
<ul>
<li style="text-align: justify;">Adobe "Upload a document and get a document ID"</li>
<li style="text-align: justify;">Adobe "Create an agreement from an uploaded document and send for signature"</li>
</ul>
<br />
<div style="text-align: justify;">
In order to be easily called, I've created the activity "When a HTTP request is received" as the trigger for the flow. The overall flow is very simple and actions are listed below.</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipveyPj2SZl0fRDkTEXvE_j2NnH93z6I-wgmPiVLLFvJ7dNpxMJw78QN0jSKm5-LSA-nBW-WFreiPelvIVMMzZloHs_l3vDp8PZ6PyLWpzi82s4sNjJpxCWoy_utFcrimzOd8exwd_ROQ/s1600/f-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="762" data-original-width="747" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipveyPj2SZl0fRDkTEXvE_j2NnH93z6I-wgmPiVLLFvJ7dNpxMJw78QN0jSKm5-LSA-nBW-WFreiPelvIVMMzZloHs_l3vDp8PZ6PyLWpzi82s4sNjJpxCWoy_utFcrimzOd8exwd_ROQ/s400/f-1.png" width="391" /></a></div>
<br />
<span style="text-align: justify;">To demonstrate you could receive the parameter, The relativePath Document/{documentName} has been added. The http call will pass the document name as parameter so flow could find the document. This is described as the below screenshot. </span><br />
<span style="text-align: justify;"><br /></span>
<span style="text-align: justify;"><br /></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXFx6bUbTW33yytcBiJrTRqk6E7LTF8khwtZP22vJ2G-9zT8h0pxcUtrKneD6hjuUITqMbs8lzSlqGTdUxg0rayszoUXyuWSuklCcaymv2WWgmeKSUsznuRTQfSJGGOQ9ytAat7uhSkjw/s1600/f-2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="491" data-original-width="691" height="283" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXFx6bUbTW33yytcBiJrTRqk6E7LTF8khwtZP22vJ2G-9zT8h0pxcUtrKneD6hjuUITqMbs8lzSlqGTdUxg0rayszoUXyuWSuklCcaymv2WWgmeKSUsznuRTQfSJGGOQ9ytAat7uhSkjw/s400/f-2.png" width="400" /></a></div>
<span style="text-align: justify;"><br /></span>
<span style="text-align: justify;">The url to call the flow is like this below. Please note I have updated the real GUID and the way pass the Document as parameter in red.</span><br />
<span style="text-align: justify;"><br /></span>
<a href="https://prod-41.westus.logic.azure.com/workflows/9452e067c6b648c3ac4ed60da4fe5650/triggers/manual/paths/invoke/Document/myDocument.docx?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=L5LqW-NVBFc6sdxHpth54mlWMZAQdePjaY62tveepQg">https://prod-41.westus.logic.azure.com/workflows/9452e067c6b648c3ac4ed60ab1234567/triggers/manual/paths/invoke/<span style="color: red;">Document/myDocument.docx</span>?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=L5LqW-NVBFc6sdxHpth</a>XXXXXXXXXX<br />
<br />
The next key is to get the file content and I found the easy way is to use "Get file content using path" action. You could use other actions but may need to get file Id. The rest actions are same as the OoO flow as we mentioned <span style="text-align: justify;">"Get signatures on a selected file in SharePoint" with few parameters modified for file content.</span><br />
<span style="text-align: justify;"><br /></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjREDkN8elEX5ULo9gEgedFb-_OlM4aOmkv2tFBUAHTSefprNtS5Z7nlMHBJ3EzGuzD8phT8sl4oN3t9gJGNW7bOhnHOaB5eZH_ktbtQAQIAk2ygN5BF0VWZRSBJ8go0LiR8A72Tn0BGrI/s1600/f-3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="839" data-original-width="693" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjREDkN8elEX5ULo9gEgedFb-_OlM4aOmkv2tFBUAHTSefprNtS5Z7nlMHBJ3EzGuzD8phT8sl4oN3t9gJGNW7bOhnHOaB5eZH_ktbtQAQIAk2ygN5BF0VWZRSBJ8go0LiR8A72Tn0BGrI/s400/f-3.png" width="330" /></a></div>
<span style="text-align: justify;"><br /></span>
<span style="text-align: justify;">Now, you have a working http service that can be invoked to integrate SharePoint denouement with Adobe Sign. </span><br />
<span style="text-align: justify;"><br /></span>
<span style="text-align: justify;">Few enhancements in the future.</span><br />
<br />
<ol>
<li><span style="text-align: justify;">Pass file ID like DocID only and the flow to retrieve file name, path, and content form the file ID.</span></li>
<li><span style="text-align: justify;">Retrieve</span><span style="text-align: justify;"> "Recipient Email" from SharePoint list item.</span></li>
<li><span style="text-align: justify;">Retrieve the Signed document and stored to SharePoint some where or update the document item with the Signed Adobe document Url.</span></li>
</ol>
<div style="text-align: justify;">
This is my first SharePoint Adobe flow, there will be more integration and enhancements we could implement in the future. </div>
<div style="text-align: justify;">
<br /></div>
<br />
<br />
<div class="MsoNormal">
<o:p></o:p></div>
<span style="text-align: justify;"><br /></span>Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0tag:blogger.com,1999:blog-965447820345581793.post-584982714275357012019-07-17T13:25:00.000-07:002019-07-23T14:01:45.642-07:00Ultimate procedures to configure "Calendar E-mail Extension for Office 365" apps for SharePoint online<br />
<div class="MsoNormal">
</div>
<div style="text-align: justify;">
<span style="background: white; color: #444444; font-family: inherit;"><a href="https://www.sharepointsapiens.com/calendar-email-extension-office365/">Calendar E-mail Extension for Office 365</a> sends
invitations, handles responses and allows Exchange resources to be booked from
a SharePoint calendar, making organizing your calendar as simple and efficient
as possible. Here is the summary of the features.</span></div>
<span style="font-family: inherit;"></span><br />
<ul>
<li><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="color: #444444;"><span style="background: white;"><span style="-webkit-text-stroke-width: 0px; float: none; font-variant-caps: normal; font-variant-ligatures: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; white-space: pre-wrap; widows: 2; word-spacing: 0px;"><b><i>Send meeting </i></b></span></span></span><span style="color: #444444;"><span style="background: white;"><span style="-webkit-text-stroke-width: 0px; float: none; font-variant-caps: normal; font-variant-ligatures: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; white-space: pre-wrap; widows: 2; word-spacing: 0px;"><b><i>requests from SharePoint calendars.</i></b> </span></span></span></span><span style="background: white;"><span style="white-space: pre-wrap;">Now it's possible </span></span><span style="color: #444444; font-family: inherit;"><span style="background: white;"><span style="white-space: pre-wrap;">to invite attendees directly from a SharePoint Online calendar.</span></span></span></span></li>
<li><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="color: #444444;"><span style="background: white;"><span style="-webkit-text-stroke-width: 0px; float: none; font-variant-caps: normal; font-variant-ligatures: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; white-space: pre-wrap; widows: 2; word-spacing: 0px;"><b><i>Book Exchange </i></b></span></span></span><span style="color: #444444;"><span style="background: white;"><span style="-webkit-text-stroke-width: 0px; float: none; font-variant-caps: normal; font-variant-ligatures: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; white-space: pre-wrap; widows: 2; word-spacing: 0px;"><b><i>rooms and equipment.</i></b> </span></span></span></span><span style="background: white;"><span style="white-space: pre-wrap;">You can book rooms </span></span><span style="color: #444444; font-family: inherit;"><span style="background: white;"><span style="white-space: pre-wrap;">and equipment in Exchange directly from a SharePoint Online calendar.</span></span></span></span></li>
<li><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="color: #444444;"><span style="background: white;"><span style="-webkit-text-stroke-width: 0px; float: none; font-variant-caps: normal; font-variant-ligatures: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; white-space: pre-wrap; widows: 2; word-spacing: 0px;"><b><i>View status </i></b></span></span></span><span style="color: #444444;"><span style="background: white;"><span style="-webkit-text-stroke-width: 0px; float: none; font-variant-caps: normal; font-variant-ligatures: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; white-space: pre-wrap; widows: 2; word-spacing: 0px;"><b><i>replies and attendance status.</i></b> </span></span></span></span><span style="background: white;"><span style="white-space: pre-wrap;">The SharePoint </span></span><span style="color: #444444; font-family: inherit;"><span style="background: white;"><span style="white-space: pre-wrap;">Online Calendar displays every attendee’s invite status, including their </span></span></span><span style="color: #444444; font-family: inherit;"><span style="background: white;"><span style="white-space: pre-wrap;">acceptance status, tentative acceptances or any new time suggested.</span></span></span></span></li>
<li><span style="font-family: inherit;"><span style="font-family: inherit;"><span style="color: #444444;"><span style="background: white;"><span style="-webkit-text-stroke-width: 0px; float: none; font-variant-caps: normal; font-variant-ligatures: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; white-space: pre-wrap; widows: 2; word-spacing: 0px;"><b><i>Check </i></b></span></span></span><span style="color: #444444;"><span style="background: white;"><span style="-webkit-text-stroke-width: 0px; float: none; font-variant-caps: normal; font-variant-ligatures: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; white-space: pre-wrap; widows: 2; word-spacing: 0px;"><b><i>availability of attendees and resources.</i></b> </span></span></span></span><span style="background: white;"><span style="white-space: pre-wrap;">You can check </span></span><span style="color: #444444; font-family: inherit;"><span style="background: white;"><span style="white-space: pre-wrap;">resources' and attendees' availability when scheduling an event directly from a </span></span></span>SharePoint Online calendar.</span></li>
</ul>
<br />
<span style="background-color: white;"><span style="color: #444444; font-family: inherit;">One of the key feature we are looking for is to
check the availability of the Exchange rooms and equipment. There are few tricks to make this work in </span><span style="color: #444444;">SharePoint</span><span style="color: #444444; font-family: inherit;"> online. Here are the
details to configured this correctly.</span></span><br />
<span style="background-color: white;"><span style="color: #444444; font-family: inherit;"><br /></span></span>
<br />
<div class="MsoNormal">
<span style="font-family: inherit;"><b><i><u>1. The first step is to add "Calendar E-mail Extension for Office 365" to SharePoint
online site collection.</u></i></b><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">You could "Add an app" from the SharePoint online site collection. Here is the app you </span>should<span style="font-family: inherit;"> add.</span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhV6JTlMglFXe6ZaCmcBTYDTw4onFscldeHYppeP45q3rj2RSLq-VU7RxcsjuHdQopkUqzLR1ATf3Eiqc_GK0mMYJ54iL0Sq_wUNLQLbjSnhvKfhNuuIs5VFy0wMYiEcHMFEmNwKfmkLzE/s1600/cal-1-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="591" data-original-width="939" height="251" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhV6JTlMglFXe6ZaCmcBTYDTw4onFscldeHYppeP45q3rj2RSLq-VU7RxcsjuHdQopkUqzLR1ATf3Eiqc_GK0mMYJ54iL0Sq_wUNLQLbjSnhvKfhNuuIs5VFy0wMYiEcHMFEmNwKfmkLzE/s400/cal-1-1.png" width="400" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><b><i><u>2. The second step is to enable the conflict checking scripting on the site collection. </u></i></b><span style="color: red;"><i>This has to be done before you configure the calendar! Otherwise you have to update each existing <o:p></o:p></i></span></span><span style="color: red;"><i>calendar and save it to pick up the conflict checking script! </i></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The two steps are:</div>
<div class="MsoNormal">
a. Enable the custom scripts on the site. The SharePoint Online Management Powershell script is like this below.</div>
<div class="MsoNormal">
<br /></div>
<pre class="de1" style="background-color: white; border: none; box-sizing: border-box; color: #434a54; font-family: monospace, serif; font-size: 13px; line-height: 1.4; margin-left: 5px; overflow: auto; padding: 0px; white-space: pre-wrap;"><span class="co2" style="box-sizing: border-box; color: teal;"># Change this URL to match your admin site</span>
Connect<span class="sy0" style="box-sizing: border-box; color: green;">-</span>SPOService <span class="sy0" style="box-sizing: border-box; color: green;">-</span>Url https<span class="sy0" style="box-sizing: border-box; color: green;">:</span><span class="co1" style="box-sizing: border-box; color: teal; font-style: italic;">//<yourdomain>-admin.sharepoint.com</span>
<span class="co2" style="box-sizing: border-box; color: teal;"># Enter username and password</span>
<span class="co2" style="box-sizing: border-box; color: teal;"># Change this URL to match your SharePoint site</span>
Set<span class="sy0" style="box-sizing: border-box; color: green;">-</span>SPOSite <span class="sy0" style="box-sizing: border-box; color: green;">-</span>Identity https<span class="sy0" style="box-sizing: border-box; color: green;">:</span><span class="co1" style="box-sizing: border-box; color: teal; font-style: italic;">//<yourdomain>.sharepoint.com/sites/yoursite -DenyAddAndCustomizePages $false</span></pre>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
b. Update <a href="https://www.sharepointsapiens.com/calendar-email-extension-office365/" style="text-align: justify;">Calendar E-mail Extension for Office 365</a><span style="background-color: white; color: #444444; text-align: justify;"> app permission. </span></div>
<div class="MsoNormal">
<span style="background-color: white; color: #444444; text-align: justify;"><br /></span></div>
<div class="MsoNormal">
</div>
<ul>
<li><span style="font-family: inherit;"><span style="background-color: white;">Open the SharePoint online site where the add-in is installed and navigate to </span><span style="background-color: white; box-sizing: border-box;">http://</span><em style="background-color: white; box-sizing: border-box;"><SharePointWebsite></em><span style="background-color: white; box-sizing: border-box;">/_layouts/15/AppInv.aspx</span><span style="background-color: white;">.</span></span></li>
<li><span style="font-family: inherit;"><span style="background-color: white;">Enter the following </span><strong style="background-color: white; box-sizing: border-box;">App Id</strong><span style="background-color: white;"> (</span><span style="background-color: white; white-space: pre-wrap;">d4f43231</span><span class="sy0" style="box-sizing: border-box; white-space: pre-wrap;">-</span><span style="background-color: white; white-space: pre-wrap;">f66d</span><span class="sy0" style="box-sizing: border-box; white-space: pre-wrap;">-</span><span style="background-color: white; white-space: pre-wrap;">4f82</span><span class="sy0" style="box-sizing: border-box; white-space: pre-wrap;">-</span><span style="background-color: white; white-space: pre-wrap;">93b1</span><span class="sy0" style="box-sizing: border-box; white-space: pre-wrap;">-</span><span style="background-color: white; white-space: pre-wrap;">bd5a9d9945c1) </span><span style="background-color: white;">and click on </span><strong style="background-color: white; box-sizing: border-box;">Lookup</strong><span style="background-color: white;">.</span></span></li>
<li><span style="background-color: white;"><span style="font-family: inherit;">In the Permission Request XML box, enter the following xml:</span></span></li>
</ul>
<br />
<pre class="de1" style="background-color: white; border: none; box-sizing: border-box; font-family: monospace, serif; font-size: 13px; line-height: 1.4; margin-left: 5px; overflow: auto; padding: 0px; white-space: pre-wrap;"><span class="sy0" style="box-sizing: border-box;"> <</span>AppPermissionRequests AllowAppOnlyPolicy<span class="sy0" style="box-sizing: border-box;">=</span><span class="st0" style="box-sizing: border-box;">"true"</span> <span class="sy0" style="box-sizing: border-box;">></span>
<span class="sy0" style="box-sizing: border-box;"><</span>AppPermissionRequest Scope<span class="sy0" style="box-sizing: border-box;">=</span><span class="st0" style="box-sizing: border-box;">"http://sharepoint/content/sitecollection/web"</span> </pre>
<pre class="de1" style="background-color: white; border: none; box-sizing: border-box; font-family: monospace, serif; font-size: 13px; line-height: 1.4; margin-left: 5px; overflow: auto; padding: 0px; white-space: pre-wrap;"> Right<span class="sy0" style="box-sizing: border-box;">=</span><span class="st0" style="box-sizing: border-box;">"FullControl"</span> <span class="sy0" style="box-sizing: border-box;">/></span>
<span class="sy0" style="box-sizing: border-box;"> </</span>AppPermissionRequests<span class="sy0" style="box-sizing: border-box;">></span></pre>
<pre class="de1" style="background-color: white; border: none; box-sizing: border-box; font-family: monospace, serif; font-size: 13px; line-height: 1.4; margin-left: 5px; overflow: auto; padding: 0px; white-space: pre-wrap;"><span class="sy0" style="box-sizing: border-box;">
</span></pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgiTpq1qbvlKXULdrWRN2O7deaYpiyho2yLlqQU4LT2n_KC7-Aas2iCDgOZTLKlQ7jdQbxI1TI8smLz_jgcgW-A3ovNhBWFeoXF9US-TmAYTmIKsT7tTKWQ26-6D4rSX099v6jBPkTeok/s1600/cal-1-2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="639" data-original-width="952" height="267" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgiTpq1qbvlKXULdrWRN2O7deaYpiyho2yLlqQU4LT2n_KC7-Aas2iCDgOZTLKlQ7jdQbxI1TI8smLz_jgcgW-A3ovNhBWFeoXF9US-TmAYTmIKsT7tTKWQ26-6D4rSX099v6jBPkTeok/s400/cal-1-2.png" width="400" /></a></div>
<div style="background-color: white; border: none; box-sizing: border-box; line-height: 1.4; margin-left: 5px; overflow: auto; padding: 0px; white-space: pre-wrap;">
</div>
<ul>
<li><span style="font-family: inherit;">Click on <span style="box-sizing: border-box; white-space: normal;">Trust it</span><span style="white-space: normal;">.</span></span></li>
<li>Click on the<span style="font-family: inherit; white-space: normal;"> </span><span style="box-sizing: border-box; font-family: inherit; white-space: normal;">Calendar E-Mail Extension for Office 365</span><span style="font-family: inherit; white-space: normal;"> </span><span style="font-family: inherit; white-space: normal;">add-in in the site contents page</span></li>
<li>Click on<span style="font-family: inherit; white-space: normal;"> </span><span style="box-sizing: border-box; font-family: inherit; white-space: normal;">Configure</span><span style="font-family: inherit; white-space: normal;"> </span><span style="font-family: inherit; white-space: normal;">on each calendar list where the add-in is configured and save the settings again.</span></li>
</ul>
<br />
<div style="background-color: white; border: none; box-sizing: border-box; color: #434a54; line-height: 1.4; margin-left: 5px; overflow: auto; padding: 0px; white-space: pre-wrap;">
<span style="font-family: inherit;"><span style="background-color: transparent;">The details is listed in vendor's documentation </span><a href="https://www.sharepointsapiens.com/calendar-email-extension-office365/documentation/check-resource-availability/" style="background-color: transparent;">here</a><span style="background-color: transparent;">.</span></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><b><i><u>3. The third step is to add calendar and configure the resources.</u></i></b><o:p></o:p></span></div>
<div class="MsoNormal">
Click Settings->Site contents->"Calendar E-mail Extension for Office 365", this will bring to configuration page like below.</div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-EkORRKobuBUYxfXA1oezgnC1lhvZl0-yqSWoxuViulBgzkDyVnaYrhBn63_f_J9drq51i3hvfLfBoWcUjdHFAl8ScpFtQ9iUuvqDpPsJuxE_YSIvS3yRNbbCNtptVI-PcZGTXjPurrQ/s1600/cal-1-3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="472" data-original-width="1342" height="140" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-EkORRKobuBUYxfXA1oezgnC1lhvZl0-yqSWoxuViulBgzkDyVnaYrhBn63_f_J9drq51i3hvfLfBoWcUjdHFAl8ScpFtQ9iUuvqDpPsJuxE_YSIvS3yRNbbCNtptVI-PcZGTXjPurrQ/s400/cal-1-3.png" width="400" /></a></div>
<div class="MsoNormal">
After add a new calendar, click "Configuration" of the calendar. Enter the account or the shared mailbox account. The enter password to verify.</div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgc9OaOCdQSHQ1uNYzCFAG4S3H0nYYvqs42ZRBWHmbdvjs_KaUaQK54oQhtkG_iKuL2q8SBnOnE4UdXHS1TSpuQecWmKspRaSDWYBYfsAfJXn4PM-wArS1bAKuSoVFoEfDiCJeQ305LtV4/s1600/cal-1-4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="623" data-original-width="1287" height="192" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgc9OaOCdQSHQ1uNYzCFAG4S3H0nYYvqs42ZRBWHmbdvjs_KaUaQK54oQhtkG_iKuL2q8SBnOnE4UdXHS1TSpuQecWmKspRaSDWYBYfsAfJXn4PM-wArS1bAKuSoVFoEfDiCJeQ305LtV4/s400/cal-1-4.png" width="400" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The you could configure the "Attendees" and "Resources". Please note you might need to add additional room and equipment mailbox for your application as in the below screenshot. Please select "Check Availability".</div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVpw-jd8rsJto3VBib-aFCK2s6f5mAWEdLzkk_TXoX5GocWdv6f_pIWC3SUWq_V-O_ysANmHP7fAtXfTp9DvY9cp9M4RY1pshCwcloMRkQhM-GCzqXkNNyoNsx9nlroEAwqD9UOIomcJo/s1600/cal-1-5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="896" data-original-width="1300" height="275" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVpw-jd8rsJto3VBib-aFCK2s6f5mAWEdLzkk_TXoX5GocWdv6f_pIWC3SUWq_V-O_ysANmHP7fAtXfTp9DvY9cp9M4RY1pshCwcloMRkQhM-GCzqXkNNyoNsx9nlroEAwqD9UOIomcJo/s400/cal-1-5.png" width="400" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: inherit;">Then you would need to click save.</span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><b><i><u>4. The forth step is to hide some fields users may not need to simplify the forms.</u></i></b></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">You could change the room to one of the </span>following<span style="font-family: inherit;"> three options. This depends on your business requirement</span></div>
<div class="MsoNormal">
</div>
<ul>
<li><span style="font-family: inherit;">Drop-Down Menu</span></li>
<li><span style="font-family: inherit;">Radio Buttons</span></li>
<li><span style="font-family: inherit;">Checkboxes (allow multiple selection)</span></li>
</ul>
<br />
<div class="MsoNormal">
<span style="font-family: inherit;">The procedure is to </span>click<span style="font-family: inherit;"> "List Settings"->Click "Event (with email extension) Content Types->"</span>Exchange<span style="font-family: inherit;"> Rooms and Equipment"-> Click "Edit column". Then select one of the three options.</span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCFdD-APN7Hi_BmGyD_cVPaptsvN1CwSe8FDfvmpLjRBARKUlnsAKvMZiAcQeIizI8fmr-eh0vOfbCaknZJh0nWMgichbGHKUHJbMmkt4QtWoCjoSQ_l-6b3xFJCsHpTucgnwvcjjTIq4/s1600/cal-1-6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="732" data-original-width="703" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCFdD-APN7Hi_BmGyD_cVPaptsvN1CwSe8FDfvmpLjRBARKUlnsAKvMZiAcQeIizI8fmr-eh0vOfbCaknZJh0nWMgichbGHKUHJbMmkt4QtWoCjoSQ_l-6b3xFJCsHpTucgnwvcjjTIq4/s400/cal-1-6.png" width="383" /></a></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">If you need to hide some files from users, please refer to my previous <a href="https://sharepointconnoisseur.blogspot.com/2019/07/easy-way-to-hide-sharepoint-online.html">blog</a> for details.</span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"></span><br />
<div class="MsoNormal" style="-webkit-text-stroke-width: 0px; color: black; font-family: "Times New Roman"; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
</div>
<br />
<div class="MsoNormal" style="orphans: 2; text-align: start; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; widows: 2;">
<div style="margin: 0px;">
<i><u><b style="-webkit-text-stroke-width: 0px; color: black; font-family: "Times New Roman"; font-size: medium; font-variant-caps: normal; font-variant-ligatures: normal; letter-spacing: normal; text-transform: none; white-space: normal; word-spacing: 0px;">5. The fifth step you might need to do is to </b><b>disable</b><b style="-webkit-text-stroke-width: 0px; color: black; font-family: "Times New Roman"; font-size: medium; font-variant-caps: normal; font-variant-ligatures: normal; letter-spacing: normal; text-transform: none; white-space: normal; word-spacing: 0px;"> users to save booking when there is a conflict.</b></u></i></div>
<div style="margin: 0px;">
<i><u><b style="-webkit-text-stroke-width: 0px; color: black; font-family: "Times New Roman"; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; text-transform: none; white-space: normal; word-spacing: 0px;"><br /></b></u></i></div>
<div style="margin: 0px;">
The default behavior after you configure to check availability of the resource, when you try to save the booking, it will prompt the dialog box when there is a conflict.</div>
<div style="margin: 0px;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjowNDytNr1qjC3IklGBv9EiUQXLpC3tr2fv4antF7xyokYyAjPqez8lKY1WVCiQOLnQWGtZgWA9yiqJ1tWhBg7rgNXNq4LzBnDfwHauemPSAG1r5k0nSNinYpwzx7slAyj9x5VyjrXDjM/s1600/cal-1-9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="229" data-original-width="699" height="130" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjowNDytNr1qjC3IklGBv9EiUQXLpC3tr2fv4antF7xyokYyAjPqez8lKY1WVCiQOLnQWGtZgWA9yiqJ1tWhBg7rgNXNq4LzBnDfwHauemPSAG1r5k0nSNinYpwzx7slAyj9x5VyjrXDjM/s400/cal-1-9.png" width="400" /></a></div>
<div style="margin: 0px;">
<br /></div>
<div style="margin: 0px;">
<br /></div>
<div style="margin: 0px;">
You will noticed that there are two buttons "Don't save" and "Save Anyway" that do not make sense to allow users to save if there is conflict. </div>
<div style="margin: 0px;">
<br /></div>
<div style="margin: 0px;">
Well, there is a way to remove these two buttons and here are the details. There are three places you need to make the change. The URLs are listed below for list named "PCP Booking 2".</div>
<div class="MsoNormal">
</div>
<ul>
<li><span style="font-size: x-small;"><a href="https://mycompany.sharepoint.com/sites/hc/Lists/PCP%20Booking%202/calendar.aspx">https://mycompany.sharepoint.com/sites/hc/Lists/PCP%20Booking%202/calendar.aspx</a></span></li>
<li><span style="font-size: x-small;"><a href="https://mycompany..sharepoint.com/sites/hc/Lists/PCP%20Booking/NewForm.aspx">https://mycompany..sharepoint.com/sites/hc/Lists/PCP%20Booking/NewForm.aspx</a></span></li>
<li><span style="font-size: x-small;"><a href="https://mycompany..sharepoint.com/sites/hc/Lists/PCP%20Booking/EditForm.aspx">https://mycompany..sharepoint.com/sites/hc/Lists/PCP%20Booking/EditForm.aspx</a></span></li>
<li><span style="font-size: x-small;"><a href="https://mycompany..sharepoint.com/sites/hc/Lists/PCP%20Booking/EditForm.aspx">https://mycompany..sharepoint.com/sites/hc/Lists/PCP%20Booking/DispForm.aspx</a></span></li>
</ul>
<br />
<div style="margin: 0px;">
Go to "Settings" -> "Edit the page" and find the "SharePoint|sapiens Calendar E-Mail Extension Client Script" webpart->Click "EDIT SNIPPET".</div>
<div style="margin: 0px;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_HPLu_C3idYi7WWG1Sihv5Rhwn57ROOn9HmjvU4GVpJ1KzCOypr-BCX1IiLexPtw11hnTLfFCGlfOd5u2GOFF9zEHYYvQOoDy-FEH6evup3mouRzIQevYorEESNBA4Tr831jNS2rFSxM/s1600/cal-1-7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="668" data-original-width="1291" height="206" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_HPLu_C3idYi7WWG1Sihv5Rhwn57ROOn9HmjvU4GVpJ1KzCOypr-BCX1IiLexPtw11hnTLfFCGlfOd5u2GOFF9zEHYYvQOoDy-FEH6evup3mouRzIQevYorEESNBA4Tr831jNS2rFSxM/s400/cal-1-7.png" width="400" /></a></div>
<div style="margin: 0px;">
<br /></div>
<div style="margin: 0px;">
Then append the following script to hide two buttons. Click "Insert" and save the page.</div>
<div style="margin: 0px;">
<br /></div>
<div class="MsoNormal">
<span lang="EN-GB" style="color: #1f497d; mso-ansi-language: EN-GB;"><span style="font-size: x-small;"> <style type="text/css"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span lang="EN-GB" style="color: #1f497d; mso-ansi-language: EN-GB;"><span style="font-size: x-small;"> input[value='Save Anyway'] { display: none; }<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span lang="EN-GB" style="color: #1f497d; mso-ansi-language: EN-GB;"><span style="font-size: x-small;"> input[value="Don't save"] { display: none; }<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span lang="EN-GB" style="color: #1f497d; mso-ansi-language: EN-GB;"><span style="font-size: x-small;"> input[value="Don't save"] + input[value='Save'] { display:
none; }<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal">
<span lang="EN-GB" style="color: #1f497d; mso-ansi-language: EN-GB;"><span style="font-size: x-small;"> </style></span><o:p></o:p></span></div>
<div style="margin: 0px;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJG-_2YElmXIlBgJT_K-xbQ5U630P8iUEkEDU8WaCif2t_6Q2INOsuKe4_nPNKukj91KncsMZrvIahma0mI8lqVIVBg4cucgNv5f8lPLmPQhPLZct80N2wMa2MHUadyTT0Vl4zE_1-qvQ/s1600/cal-1-8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="347" data-original-width="678" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJG-_2YElmXIlBgJT_K-xbQ5U630P8iUEkEDU8WaCif2t_6Q2INOsuKe4_nPNKukj91KncsMZrvIahma0mI8lqVIVBg4cucgNv5f8lPLmPQhPLZct80N2wMa2MHUadyTT0Vl4zE_1-qvQ/s400/cal-1-8.png" width="400" /></a></div>
<div style="margin: 0px;">
<br /></div>
<div style="margin: 0px;">
<i><u><b style="font-family: "times new roman";">5. The sixth optional step you might want to do prevent users to create past event.You could configure this in the lsit Validation setting.</b></u></i><br />
<i><u><b style="font-family: "times new roman";"><br /></b></u></i>
<span style="font-family: inherit;">Click the list settings->Validation settings. Enter the following formula to prevent past event to be </span>created<span style="font-family: inherit;">.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-size: x-small;"><b><i>=[End Time]>Created</i></b></span><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBvbJNfvNc2NKa9fMjgGV1w2CtAsXYe1Okr013u36WEUbanmREOX1kWhquHQzHejKxNXEewUV6luLBQ_RiFhi1Ww37hXkYPBll6UPRBemQAmzqL1LoVkOX130ZafecXTW5IAILcRdXIeg/s1600/cal-1-10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="727" data-original-width="785" height="370" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBvbJNfvNc2NKa9fMjgGV1w2CtAsXYe1Okr013u36WEUbanmREOX1kWhquHQzHejKxNXEewUV6luLBQ_RiFhi1Ww37hXkYPBll6UPRBemQAmzqL1LoVkOX130ZafecXTW5IAILcRdXIeg/s400/cal-1-10.png" width="400" /></a></div>
<br />
You could use another formula to prevent the past event to be modified.<br />
<br />
<span style="font-family: "calibri" , sans-serif; font-size: 11.0pt;">=[End Time]>Now() </span><br />
<i><u><b style="font-family: "times new roman";"><br /></b></u></i>
<i><u><span><b style="font-family: "times new roman";">5. The seventh optional step you might want to allow user to edit their events </b><span style="font-family: times new roman;"><b>created</b></span><b style="font-family: "times new roman";"> by themselves.</b></span></u></i><br />
<i><u><b style="font-family: "times new roman";"><br /></b></u></i>
<span style="font-family: inherit;"><span>You </span><span style="background-color: white; color: #333333;">can go into the </span><strong style="background-color: white; border: 0px; color: #333333; margin: 0px; outline: 0px; padding: 0px;">List Settings</strong><span style="background-color: white; color: #333333;">, </span><strong style="background-color: white; border: 0px; color: #333333; margin: 0px; outline: 0px; padding: 0px;">Advanced Settings</strong><span style="background-color: white; color: #333333;">, and you should be able to set Read and Edit access to items that the user has created.</span></span><br />
<i><u><b style="font-family: "times new roman";"><br /></b></u></i>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwIH7jGQWDsUsNV23DP8JM5JTAWc37xVE_B-FS-tYaYNq8-964RMJFlRsNOb8BYUWAAp0D8g0-yJLMjEDcsvVQyukhXY3w55NIpZjBwKiggYf_gnxF-iOi_xCF7NjOZ2tLBJVxjPaNbgU/s1600/p2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="231" data-original-width="408" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwIH7jGQWDsUsNV23DP8JM5JTAWc37xVE_B-FS-tYaYNq8-964RMJFlRsNOb8BYUWAAp0D8g0-yJLMjEDcsvVQyukhXY3w55NIpZjBwKiggYf_gnxF-iOi_xCF7NjOZ2tLBJVxjPaNbgU/s400/p2.png" width="400" /></a></div>
<i><u><b style="font-family: "times new roman";"><br /></b></u></i>
<i><u><b style="font-family: "times new roman";"><br /></b></u></i>
<i><u><b style="font-family: "times new roman";"><br /></b></u></i></div>
</div>
</div>
<div class="MsoNormal">
<span style="font-family: inherit;">Now the </span> "Calendar E-mail Extension for Office 365" apps is ready on the SharePoint online site.</div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<o:p></o:p></div>
<br />
<br />
<br />Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com3tag:blogger.com,1999:blog-965447820345581793.post-500153970281209192019-07-16T16:39:00.001-07:002019-07-19T10:41:27.376-07:00Easy way to hide SharePoint online event list some fields like “All Day Event” and “Recurrence”<div style="text-align: justify;">
When we create a new calendar that is an event list on SharePoint online site, as default it will display fields like <b>"All Day Event"</b> and <b>"Recurrence"</b> as in the below screenshot. In some cases, you may not want these fields and here are different ways to hide them.</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPw2AN2Zz6IsR5iL8UWQkXGT-NkBGctDITYkTksCG5mLpIblAHnrW9CJHGtUAlYN2RNj-_M373n3vVL1t0-xOt9vVc-YEGjwTUDWt2tJpuDzQ4_hb-Xx4TCr92lsUo-kST9YLIXbhQnGI/s1600/cal-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="784" data-original-width="694" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPw2AN2Zz6IsR5iL8UWQkXGT-NkBGctDITYkTksCG5mLpIblAHnrW9CJHGtUAlYN2RNj-_M373n3vVL1t0-xOt9vVc-YEGjwTUDWt2tJpuDzQ4_hb-Xx4TCr92lsUo-kST9YLIXbhQnGI/s400/cal-1.png" width="353" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><b><i><u>1. The most common way is to use SharePoint designer to create new form and then remove the fields in the advanced design.</u></i></b> This has been described <a href="https://promx.net/en/2016/09/how-to-hide-columns-from-sharepoint-forms/">here</a> in details.</span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;">The major challenge is the designer is not reliable toll and you might constantly get the following error <span style="color: red;">"<span style="background-color: white;">could not save the list changes to the server"</span></span><span style="background-color: white; color: #242729;"> like discussed <a href="https://sharepoint.stackexchange.com/questions/205243/could-not-save-the-list-changes-to-the-server-in-designer">here</a>. I'm always getting this error and could not resolve it quickly. This is the way I would NOT recommend you to try!</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><span style="background-color: white; color: #242729;"><br /></span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><span style="background-color: white; color: #242729;"><b><i><u>2. The second way is to use Powershell to set the fields like described <a href="https://sharepoint.stackexchange.com/questions/77986/hide-some-fields-in-the-newform-aspx-for-a-custom-list">here</a>.</u></i></b> The key Powershell command is like below.</span></span></div>
<div style="text-align: justify;">
<span style="font-family: inherit;"><span style="background-color: white; color: #242729;"><br /></span></span></div>
<pre class="default prettyprint prettyprinted" style="background-color: #eff0f1; border-radius: 3px; border: 0px; box-sizing: inherit; color: #393318; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 1em; max-height: 600px; overflow-wrap: normal; overflow: auto; padding: 12px 8px; vertical-align: baseline; width: auto;"><code style="border: 0px; box-sizing: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;"><span class="pln" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> $field </span><span class="pun" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span><span class="pln" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> $list</span><span class="pun" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">.</span><span class="typ" style="border: 0px; box-sizing: inherit; color: #2b91af; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Fields</span><span class="pun" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">[</span><span class="str" style="border: 0px; box-sizing: inherit; color: #7d2727; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">"FieldName"</span><span class="pun" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">]</span><span class="pln" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">
$field</span><span class="pun" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">.</span><span class="typ" style="border: 0px; box-sizing: inherit; color: #2b91af; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">ShowInNewForm</span><span class="pln" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> </span><span class="pun" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">=</span><span class="pln" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> $false
$field</span><span class="pun" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">.</span><span class="typ" style="border: 0px; box-sizing: inherit; color: #2b91af; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Update</span><span class="pun" style="border: 0px; box-sizing: inherit; color: #303336; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">()</span></code></pre>
<div style="text-align: justify;">
<span style="background-color: white;"><span style="color: #242729; font-family: inherit;"><span style="font-size: 15px;">However, I'm not able to find the equivalent PnP </span></span><span style="color: #242729;"><span style="font-size: 15px;">SharePoint</span></span><span style="color: #242729;"><span style="font-family: inherit;"><span style="font-size: 15px;"> online command. If you do, please let me know. This Powershell command seems only works for </span></span><span style="font-size: 15px;">SharePoint</span><span style="font-family: inherit;"><span style="font-size: 15px;"> on-premises.</span></span></span></span></div>
<div style="text-align: justify;">
<span style="background-color: white;"><span style="color: #242729; font-family: inherit;"><span style="font-size: 15px;"><br /></span></span></span></div>
<div style="text-align: justify;">
<span style="background-color: white;"><span style="color: #242729; font-family: inherit;"><span style="font-size: 15px;"><b><i><u>3. The third way is to use the OoB UI with few <a href="https://sharepoint.stackexchange.com/questions/122987/how-do-you-hide-all-day-event-and-recurrence-from-event-content-type%20%20(Good%20one)">tricks</a>.</u></i></b> Here is the details.</span></span></span></div>
<div style="text-align: justify;">
</div>
<ul>
<li><span style="background-color: white;"><span style="color: #242729; font-family: inherit;"><span style="font-size: 15px;">Go to "List Settings" -> Advanced settings" -> </span></span></span>Set “Allow management of content types?” to “Yes”.</li>
<li>Click the content type used in the event list and click one
field with link like “<b>Category</b>”. You will be able to make the field “Hidden”. </li>
<li>Two fields like “All Day Event” and “Recurrence” do not have direct link you could modify. You should copy the URL from previous "Category" field like this.</li>
</ul>
<div>
</div>
<br />
<div class="MsoNormal">
<a href="https://mycompany.sharepoint.com/sites/ittest/site1/lit/_layouts/15/ManageContentTypeField.aspx?ctype=0x01020028CF0843C06A41A39C183B3C72AF843700CA81E905638F3F458F75A6B452C36053&List=cb607f06-a7ac-465a-847e-5ab7f178ed16&Field=Category&Fid=%7B6df9bd52%2D550e%2D4a30%2Dbc31%2Da4366832a87d%7D"><span style="font-size: x-small;">https://mycompany.sharepoint.com/sites/ittest/site1/lit/_layouts/15/ManageContentTypeField.aspx?ctype=0x01020028CF0843C06A41A39C183B3C72AF843700CA81E905638F3F458F75A6B452C36053&List=cb607f06-a7ac-465a-847e-5ab7f178ed16&Field=Category&Fid=%7B6df9bd52%2D550e%2D4a30%2Dbc31%2Da4366832a87d%7D</span></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
</div>
<ul>
<li><span style="color: red;"><b>You could now remove the "Fid" Parameter and
replace "Field" Parameter value with either "fRecurrence"
or "fAllDayEvent". </b></span></li>
</ul>
<o:p></o:p><br />
<div style="text-align: justify;">
<span style="background-color: white;"><span style="color: #242729; font-family: inherit;"><span style="font-size: 15px;"><br /></span></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://www.blogger.com/%20https:/mycompany.sharepoint.com/sites/ittest/site1/lit/_layouts/15/ManageContentTypeField.aspx?ctype=0x01020028CF0843C06A41A39C183B3C72AF843700CA81E905638F3F458F75A6B452C36053&List=cb607f06-a7ac-465a-847e-5ab7f178ed16&Field=fAllDayEvent%20">https://mycompany.sharepoint.com/sites/ittest/site1/lit/_layouts/15/ManageContentTypeField.aspx?ctype=0x01020028CF0843C06A41A39C183B3C72AF843700CA81E905638F3F458F75A6B452C36053&List=cb607f06-a7ac-465a-847e-5ab7f178ed16&Field=fAllDayEvent
</a><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div style="text-align: justify;">
</div>
<div class="MsoNormal">
<a href="https://www.blogger.com/%20https:/mycompany.sharepoint.com/sites/ittest/site1/lit/_layouts/15/ManageContentTypeField.aspx?ctype=0x01020028CF0843C06A41A39C183B3C72AF843700CA81E905638F3F458F75A6B452C36053&List=cb607f06-a7ac-465a-847e-5ab7f178ed16&Field=fRecurrence"><span style="font-size: x-small;">https://mycompany.sharepoint.com/sites/ittest/site1/lit/_layouts/15/ManageContentTypeField.aspx?ctype=0x01020028CF0843C06A41A39C183B3C72AF843700CA81E905638F3F458F75A6B452C36053&List=cb607f06-a7ac-465a-847e-5ab7f178ed16&Field=fRecurrence</span></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><i><u><span style="color: red;">Please note if you hide the <span style="text-align: justify;">“All Day Event”</span><span style="text-align: justify;"> on the content type as in method #3, the calendar cannot be displayed in the event webparts! The events will be empty.</span></span></u></i></b></div>
<div class="MsoNormal">
<br />
<b style="background-color: white; color: #242729; font-size: 15px; text-align: justify;"><i><u>4. The forth way is to use the javascript if this is allowed on the SPO site. Here is the <a href="https://myclassbook.org/hide-sharepoint-field-column/">details</a>.</u></i></b><br />
<b style="background-color: white; color: #242729; font-size: 15px; text-align: justify;"><i><u><br /></u></i></b>
<span style="background-color: white; text-align: justify;"><span style="color: #242729;"><span style="font-size: 15px;">Add a content editor webpart on respective pages (NewForm.aspx/ DispForm.aspx/ EditForm.aspx) and use below jQuery code to hide SharePoint field. </span></span></span><br />
<span style="background-color: white; text-align: justify;"><span style="color: #242729;"><span style="font-size: 15px;"><br /></span></span></span>
<pre style="margin-bottom: 20.2pt; vertical-align: baseline;"><span style="font-size: x-small;"><script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script>
$(document).ready(function(){
$('nobr:contains("Category")').closest('tr').hide(); //here we are hiding field named "Category"
$('nobr:contains("All Day Event")').closest('tr').hide(); //here we are hiding field named "All Day Event"
$('nobr:contains("Recurrence")').closest('tr').hide(); //here we are hiding field named "Recurrence"
});
</script></span></pre>
This method will hide the fields and still allow the event webpart to display the calendar events. However, this is only applicable when script is allowed on the site. If not, you could enabled it.<br />
<br />
Now you should be able to hide <span style="text-align: justify;"> </span><span style="text-align: justify;">“All Day Event” and “Recurrence” along with other fields you want to hide from end users.</span></div>
<div class="MsoNormal">
<span style="text-align: justify;"><br /></span></div>
<div class="MsoNormal">
There should be other ways like Rest/Graph API to hide, or may be SPFx customization you can try. </div>
<div style="text-align: justify;">
<span style="background-color: white;"><span style="color: #242729; font-family: inherit;"><span style="font-size: 15px;"><br /></span></span></span></div>
<div style="text-align: justify;">
<span style="background-color: white;"><span style="color: #242729; font-family: inherit;"><span style="font-size: 15px;"><br /></span></span></span></div>
<div style="text-align: justify;">
<span style="background-color: white;"><span style="color: #242729; font-family: inherit;"><span style="font-size: 15px;"><br /></span></span></span></div>
<div style="text-align: justify;">
<span style="background-color: white;"><span style="color: #242729; font-family: inherit;"><span style="font-size: 15px;"><br /></span></span></span></div>
<div style="text-align: justify;">
<br /></div>
Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com1tag:blogger.com,1999:blog-965447820345581793.post-54597449070662604172019-06-13T14:47:00.002-07:002019-06-13T14:47:37.763-07:00Procedure to use new custom properties to SPFx ListView Command project<div style="background-color: white; box-sizing: border-box; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; margin-bottom: 16px;">
If you need to add a new custom property like "sampleTextThree" with value "This is new text." to be used in SPFX extension ListView Command project. You should following the following steps.</div>
<div style="background-color: white; box-sizing: border-box; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; margin-bottom: 16px;">
<i><u><b>1. Add sampleTextThree as string in the interface</b></u></i></div>
sampleTextOne: string;<br />sampleTextTwo: string;<br /><span style="box-sizing: border-box; font-weight: 600;">sampleTextThree: string;</span><div>
<span style="box-sizing: border-box; font-weight: 600;"><span style="background-color: white; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol";"><br /></span></span></div>
<div>
<span style="box-sizing: border-box;"><span style="background-color: white; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol";"><i><u><b>2. Add property to serve.json like below.</b></u></i></span></span></div>
<div>
<span style="box-sizing: border-box;"><span style="color: #24292e; font-family: -apple-system, BlinkMacSystemFont, Segoe UI, Helvetica, Arial, sans-serif, Apple Color Emoji, Segoe UI Emoji, Segoe UI Symbol;"><br /></span></span>"default": {<br />"pageUrl": "<a href="https://mysompont.sharepoint.com/sites/HarryTestX/Shared%20Documents/Forms/AllItems.aspx" rel="nofollow" style="background-color: transparent; box-sizing: border-box; color: #0366d6; text-decoration-line: none;">https://mysompont.sharepoint.com/sites/HarryTestX/Shared%20Documents/Forms/AllItems.aspx</a>",<br />"customActions": {<br />"4de6483f-cccf-465d-b0ee-4aa6ebb0eb81": {<br />"location": "ClientSideExtension.ListViewCommandSet.CommandBar",<br />"properties": {<br />"sampleTextOne": "One item is selected in the list",<br />"sampleTextTwo": "This command is always visible.",<br /><span style="box-sizing: border-box; font-weight: 600;">"sampleTextThree": "This is new text."</span><br />...</div>
<div>
<span style="background-color: white; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol";"><br /></span></div>
<div>
<span style="background-color: white; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol";"><b><i><u>3. Change the default alert to use new property.</u></i></b></span></div>
<div>
<span style="color: #24292e; font-family: -apple-system, BlinkMacSystemFont, Segoe UI, Helvetica, Arial, sans-serif, Apple Color Emoji, Segoe UI Emoji, Segoe UI Symbol;"><br /></span>switch (event.itemId) {<br />case 'COMMAND_3':<br />Dialog.alert(<code style="background-color: rgba(27, 31, 35, 0.05); border-radius: 3px; box-sizing: border-box; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, Courier, monospace; margin: 0px; padding: 0.2em 0.4em;">${this.properties.sampleTextThree}</code>);<br />...</div>
<div>
<span style="background-color: white; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol";"><br /></span></div>
<div>
<span style="background-color: white; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol";"><b><i><u>4. Gulp serve run locally is fine.</u></i></b> However, after this deployed to tenant, the property cannot be found and is "null" as indicated in <a href="https://github.com/SharePoint/sp-dev-docs/issues/4144">here</a>.</span></div>
<div>
<span style="color: #24292e; font-family: -apple-system, BlinkMacSystemFont, Segoe UI, Helvetica, Arial, sans-serif, Apple Color Emoji, Segoe UI Emoji, Segoe UI Symbol;"><br /></span><div style="background-color: white; box-sizing: border-box; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; margin-bottom: 16px;">
You should add another step#5.</div>
<div style="background-color: white; box-sizing: border-box; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; margin-bottom: 16px;">
<b><i><u>5. Add the new custom property to "elements.xml" like below.</u></i></b></div>
<pre style="background-color: #f6f8fa; border-radius: 3px; box-sizing: border-box; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, Courier, monospace; font-size: 11.9px; line-height: 1.45; margin-bottom: 16px; overflow-wrap: normal; overflow: auto; padding: 16px;"><code style="background: transparent; border-radius: 3px; border: 0px; box-sizing: border-box; display: inline; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, Courier, monospace; line-height: inherit; margin: 0px; overflow-wrap: normal; overflow: visible; padding: 0px; word-break: normal;"> ClientSideComponentProperties="{&quot;sampleTextOne&quot;:&quot;One item is selected in the list.&quot;, &quot;sampleTextTwo&quot;:&quot;This command is always visible.&quot;**, &quot;sampleTextThree&quot;:&quot;This command is new text.&quot;**}"></code></pre>
<div style="background-color: white; box-sizing: border-box; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; margin-bottom: 16px;">
<br /></div>
<div style="background-color: white; box-sizing: border-box; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; margin-bottom: 16px;">
From my understanding, the serve.json is used by run locally "gulp serve" and the elements.xml is used by SharePoint online tenant. At this point, the new property can be picked up locally or deployed to SPO tenant.</div>
<div style="background-color: white; box-sizing: border-box; color: #24292e; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; margin-bottom: 16px;">
I'm hoping the SPFx can enhance the build process to incorporate the configuration from serve.json and build to element.xml.</div>
</div>
Harry Chenhttp://www.blogger.com/profile/12656962476070804073noreply@blogger.com0