Skip to content

Add and delete checkboxes values to datacollection when Check and Uncheck checkboxes

OnCheck

Collect(DataCollection, FiedName: ThisItem.CheckboxName)

UnCheck

RemoveIf(DataCollection, FiedName: ThisItem.CheckboxName)

PowerApps cascading dropdown list with blank value by default

I have two dropdowns, Dropdown 1 and Dropdown 2. Data source for First dropdown is table A, and data source for second dropdown is table B and filters the values based on the value of dropdown 1.

To set the default value of dropdown 2 is

  1. Navigate to Dropdown 1 OnSelect property, add the following:

Collect(ClearCollect(Collection2,{Result:””}),Sort(Distinct(Filter(SharepointTable,
ColumnName1=dropdown1.Selected.Value),ColumnName2).Result,Result))
2. Navigate to Dropdown 2 items property and then add collection name:

Collection2

Display Office365 users in PowerApps Combobox

  1. First,create a Office365Users data connection in PowerApps application.
  2. Add a datacard in powerapps and then add a combobox to the card.
  3. Select combobox and then go to Items property and then add below function to see the users email accounts(If you want to see the users’ names, then you need to select “DisplayName”) Sort(Upper(Office365Users.SearchUser({searchTerm:UserComboBox.SearchText}).Mail),Mail)
  4. To make the combobox searchable, go to combobox properties and then enable “Allow Searching” as shown in picture.
  5. To show the blank values when a new form loads or when you select the record from the gallery, and if there is no value in the datable, use the following code. If(IsBlank(ThisItem.UserEmail),Sort(Upper(Office365Users.SearchUser({searchTerm:Blank()}).Mail),Mail),Upper(Office365Users.SearchUser({searchTerm: Parent.Default}).Mail))
  6. To save the selected value to database when you submit the form, add below code to Datacard Update property. UserComboBox.Selected.Mail

Format text in PowerApps

To format text in Power Apps, use HTML text instead of Text label.

Go to ->Insert Text->Select HTML Text.

Ex: <font color=red>Text..</font>

Export Data from PowerApps Gallery to Excel

  1. First Create a excel template with column headers in SharePoint library/Shared drive.

For my requirement, following are the Column Headers:

Id, Log Types, Date Entered, Date of Effort, Effort Hours, Comments.

2. Create a Flow in Power automate as shown below:

3. In power apps application, add a button, then write below functions for OnClick property.

//Set a Header variable
Set(HeaderVar,”Id,Log Types,Date Entered,Date of Effort,Effort Hours,Comments”);
//Clear the collection

Clear(EffortLogColl);

//Collect the data for all records within the gallery
ForAll(BrowseGallery1.AllItems,Collect(EffortLogColl,{DofEffortColl:DofEffortGall.Text,ElogTypesColl:ELogTypesGall.Text,IntakeIDColl:IntakeIDGall.Text,DEnteredColl:DEnteredGall.Text,EffortHoursColl:EffortHoursGall.Text,ELCommentsColl:ELCommentsGall.Text}));

//Create a variable to store Header variable and collection data
Set(ExportCSVVar,””);
Set(ExportCSVVar,HeaderVar&Char(10)&Concat(EffortLogColl,IntakeIDColl&”,”&ElogTypesColl&”,”&DEnteredColl&”,”&DofEffortColl&”,”&EffortHoursColl&”,”&ELCommentsColl&Char(10)));


//Set(SuccessVar,ExportToExcel.Run(“EffortLog”,ExportCSVVar).Completed));
//ExportToExcel.Run(“CSVFile”,ExportCSVVar,Today());
//SaveToSP.Run(“CSVFile”,ExportCSVVar,ExportCSVVar)

//Run the flow and then get the link and save it to variable
Set(SuccessVar,SaveToSP.Run(“CSVFile”,ExportCSVVar,ExportCSVVar).completed);

//Use Launch to open the file in browser
If(SuccessVar,Launch(“https://test.sharepoint.com/:x:/r/sites/TestSite/Shared%20Documents/CSVFile.CSV?d=w4d3c75a0c1974b0f9f0339f4dd466b41&csf=1&web=1&e=2Pcwpc&Download=1&#8221;))

Copy Attachments from Datavaerse to SharePoint

If you use Dataverse as the database and if you attach files to canvas/model driven based application, then all the files will be stored to Notes table. So if you want to extract the files and store them in SharePoint then follow below steps.

base64ToBinary(triggerOutputs()?[‘body/documentbody’])

How to check if variable is empty in power automate?

  1. First initialize variable then use expression as shown below in if condition.

Dataverse Multi choice field value in Power Apps

Dataverse Multi choice columns are different, we cannot get selected value in a label.

Use concat function to get multi choice value.

Concat(ThisItem.ColumnName,Value & “”)

If you use choice field in If condition:

If(ComboBox2.Selected.Value=ChoiceColumn.'Choice A',"Yes",If(ComboBox2.Selected.Value=ChoiceColumn.'Choice B',"No"))

Get Dataverse Choice field Text in Power Automate

Follow below steps to get Dataverse choice field value in power automate.

  1. Get Row by ID

2. The below flow will just assign the Status Choice value field as 0/1. You can see in the Run History.

3. Initialize variable then click on ellipses (3 dots) then navigate to Peek Code

4. The code will look like the below. For Status the value property is assigned as below “value”: “@{outputs(‘Get_a_row_by_ID’)?[‘body/statecode’]}”

5.Update the code syntax as below.

outputs('Get_a_row_by_ID')['body/statecode@OData.Community.Display.V1.FormattedValue']

6. Then it will look like as below:

7. Output will be as follows:

Nested AND & OR in CAML Query

var web = clientContext.get_web();
var oList = clientContext.get_web().get_lists().getByTitle(‘List Display Name’);          clientContext.load(oList);                                                                                                                  var camlQuery = new SP.CamlQuery();

query =                                                                                                                                                      “<View Scope=’RecursiveAll’>
<Query>
<OrderBy>
<FieldRef Name=’WeekdayNumber’ Ascending=’TRUE’ />
</OrderBy>
<GroupBy Collapse=’TRUE’ GroupLimit=’100′>
<FieldRef Name=’WeekdayNumber’ />
<FieldRef Name=’Type’ />
</GroupBy>
<Where>
<Or>
<And>                                                                                                                                                        <Neq>                                                                                                                                                        <FieldRef Name =’PriorityType’/>                                                                                                      <Value Type=’Choice’>DropdownValue1</Value>                                                                            </Neq>                                                                                                                                                      <And>
<Contains>
<FieldRef Name =’DisplayCalendars’/>
<Value Type=’Text’>Weekly Calendar(CheckboxValue)</Value>
</Contains>
<And>
<Eq>
<FieldRef Name =’IsDarkDelivery’/>
<Value Type=’Choice’>No</Value>
</Eq>
<And>
<Geq>
<FieldRef Name=’DeliveryDate’/>
<Value Type=’DateTime’ IncludeTimeValue=’FALSE’>” + firstDayOfWeek + “</Value>
</Geq>
<Lt>
<FieldRef Name=’DeliveryDate’/>
<Value Type=’DateTime’ IncludeTimeValue=’FALSE’>” + lastDayOfWeek + “</Value>
</Lt>
</And>
</And>
</And>
</And>
<And>
<Contains>
<FieldRef Name =’DisplayCalendars’/>
<Value Type=’Text’>WeeklyCalendar</Value>
</Contains>
<And>
<Eq>
<FieldRef Name=’DeliveryDate’ />
<Value Type=’DateTime’ IncludeTimeValue=’FALSE’><Today/></Value>
</Eq>
<Eq>
<FieldRef Name =’IsDarkDelivery’/>
<Value Type=’Text’>Yes</Value>
</Eq>
</And>
</And>
</Or></Where></Query></View>”;

camlQuery.set_viewXml(query);

this.collListItem = oList.getItems(camlQuery);

clientContext.load(collListItem);

clientContext.executeQueryAsync(onQuerySucceeded, onQueryFailed)

}