Conditional Formatting Lists

You can design lists with a condition with the If Formula in the gallery. It is possible to control a lot of features of the objects like color, look, size and place with these conditions. You can authorize based on person/department , hide/show buttons on the screen and highlight status in tracking processes.

Designing Lists with a Condition

In this example, items indicated with colors depending on the information they contain. While all the items of the “Education” department are indicated with purple, the Sales department is displayed with Grey.

ThisItem word is used to individually evaluate each item in the gallery. This way, it is possible to access the data of a certain row among the items.

While registering, people enter their names and surnames separately. And the & concatenation operator is used to write the name and surname next to each other.

ThisItem.P_name & ” ” & ThisItem.P_surname

Adding a Condition

An empty Label is added to the gallery and placed on the left of the gallery to highlight the items.

If(ThisItem.P_departman=”Education”;Purple; Gray) is written into the Fill property of the Label.

With this syntax, each row in the gallery will be evaluated one by one and the department info will be checked. If the department is Education, it will be displayed with Purple. If it is another department, it will be displayed with Grey.

As well as you can write the color as Purple, Red, Blue; you can also write it through the RGB codes.

When you want to use a special color code, the RGBA() function is used.
This function is written as RGBA(255; 255 ; 255 ; 1) as well. The last parameter of the formula gets a value between 0 and 1 and affects the transparency of the color.

You can click here for the other Power Apps articles.

You can click here for the details of the use of the If function.

CONDITIONAL ACTIONS WITH THE IF DECISION STRUCTURE

In this article I will be talking about a must decision/condition structure in macros. At the end of the article, we’ll have learned what IF Decision structure is and how and when to use it.

 

WHAT IS THE IF DECISION STRUCTURE?

This

condition structure is the structure that exists is all programming languages and allows the code blocks to run separately when certain conditions are met or not.

Allows you to execute actions depending on the True/False Boolean value that the indicated condition returns. If the condition is said to be True, the statements under the indicated condition(s) are applied. If the condition is said to be False, the statements indicated after the IF structure are applied. In addition, depending on the situation, if we have indicated the Else or ElseIF statements the control of those rows are done as well and when the outcome returns as True, the indicated commands are applied.

 

WHEN TO USE?

We use this structure when we have a thought/condition that starts with If or if we want to execute a multiple-condition action while coding.  There are a few other Decision/Condition structures other than this one; Select Case, IIF, Choose and Switch structures. Having knowledge of these structure will help you while coding.

 

HOW TO USE?

First, let’s take a look at its syntax, i.e. how we can write this IF structure on VBA.

 

SYNTAX

Here is its syntax.
Blue parts cannot be changed.
Red parts can be changed.

If condition(s) Then
‘Your codes
End If

You can think of its logic as “If this number is bigger than 10, do this…

If we have one condition only, you can use the syntax like this too:
If condition(s) Then ‘Your codes
If there is only one condition, you can remove the End If statement and write the condition next to the Then statement.
The conditions between If and Then statements can be multiple depending on the situation.
For example; If the numbers in the cells of the A column are bigger than 10 and not blank. You can use logical operators like And, Or, Xor.

Here is a little code sample for you to understand this part completely

If sayi > 10 And sayi < 20 Then

You can increase the condition to 2-3-4… by using logical operator in a If .. Then statement. Let’s say that we have some numbers in the A2:A10 call range and we want to write “bigger” on the B column of the numbers bigger 10.

Here is the code we need for this:

Sub IF_Kosul_Yapisi()
    For i = 2 To 10
        If Cells(i, "A") > 10 Then
            Cells(i, "B") = "Büyük"
        End If
    Next i
End Sub

You can see how these codes work in the GIF below.

IF structure

 

MULTIPLE CONDITIONS AND ELSE

If we have multiple conditions and you logically don’t want to execute actions depending on two situations like If it is like this do this, if not do that; you can write this in a line like this:

If Cells(i, "A") > 10 Then Cells(i, "B") = "Büyük" Else Cells(i, "B") = "Küçük"

and you can also write them one under the other with the Else statement. Then, your syntax will be like this:

If condition(s) Then
‘Your Codes If True
Else
‘Your Codes If False
End If

Some sample codes..

If Cells(i, "A") > 10 Then
    Cells(i, "B") = "Büyük"
        Else
    Cells(i, "B") = "Küçük"
End If

The code above writes Bigger to the B column of cells bigger than 10, and Smaller to the smaller ones.

 

ELSEIF

Now, let’s increase out conditions by including the ElseIf statement.
You can think the logic of this syntax as “If it’s like this to this; if not and it’s like that, do that; if not and it’s like the other way, then do that way.” 

Here is the syntax..
If conditions Then
‘your codes
ElseIf conditions Then
‘your codes
ElseIf conditions Then
‘your codes
End If

Let’s have our outcomes written to the Outcome column according to these conditions.

  • If the number is equal to zero, it will say Zero.
  • If the number is equal to 10, it will say Equal.
  • If the number is smaller than 10, it will say Smaller.
  • If the number is bigger than 10, it will say Bigger.

Depending on the conditions we’ve stated, the code we’ll need will be:

Sub Eger_Kosul_Yapısı_2()
    Dim i As Integer
    For i = 2 To Range("A65536").End(3).Row
        If Cells(i, "A") = 0 Then
            Cells(i, "B") = "Sıfır"
        ElseIf Cells(i, "A") = 10 Then
            Cells(i, "B") = "Eşit"
        ElseIf Cells(i, "A") < 10 Then
            Cells(i, "B") = "Küçük"
        ElseIf Cells(i, "A") > 10 Then
            Cells(i, "B") = "Büyük"
        End If
    Next i
End Sub

You can see how these codes work in the GIF below as well.

 

ELSEIF and ELSE TOGETHER

Lastly, if none of the conditions above are suitable for our codes, then we can include the Else statement before the End If row and get another outcome written in the cell as well.

Sub Eger_Kosul_Yapısı_3()
    Dim i As Integer
    For i = 2 To Range("A65536").End(3).Row
        If Cells(i, "A") = 0 Then
            Cells(i, "B") = "Sıfır"
        ElseIf Cells(i, "A") = 10 Then
            Cells(i, "B") = "Eşit"
        ElseIf Cells(i, "A") < 10 Then
            Cells(i, "B") = "Küçük"
        Else
            Cells(i, "B") = "Büyük"
        End If
    Next i
End Sub

 

You can take a look at the Condition Structures in Visual Basic for more details.

 

See you in other articles, bye. 🙋🏻‍♂️
You can share this article with your friends and get them informed as well. 👍🏻

Manage Formulas by Creating a Formula Screen

You can have an interactive flow or execute visual actions by creating rules with formulas in application screens.

Usually hide, show, freeze, open to edit or change color, location, size actions are done for the determined objects. You can apply all these controls in all application scenarios easily and flexibly.

Depending on the situation in the application scenario, you can connect objects or actions to department, user mail, location, title, marital status or user choice in multiple-choice actions.You can apply all these controls in all application scenarios easily and flexibly.

In this article, we will be talking about managing the conditions in the screen centrally and formula evaluation duration. And most importantly, we will talk about the importance of executing an action after the formula evaluation duration is done.

What is Formula Evaluation?

Formula evaluation is the test done during the result that is created by the written formula. Depending on the performance of the formula, a certain duration is needed.

Why Does Formula Evaluation Takes Too Long?

Depending on the performance of the formula, duration will be longer or shorter. As the conditions in the screen get more complex; main reasons like using nested formulas, waiting for the control of  different objects related to each other to be complete, and executing an action depending on the respond from the data source can make the duration of formula evaluation longer.

For this reason, it is important to make the formula evaluation run as fast as possible. When the respond of a formula that hasn’t been completely evaluated yet is late, the default value can be accepted as a respond and cause wrong actions.

What if a Formula is not Evaluated?

When the respond of a formula that hasn’t been completely evaluated yet is late, the default value can be accepted as a respond and cause wrong actions. Moving on to the next action before the formula evaluation is complete will cause incorrect results. Which means that even the condition you’ve specified is met, since the respond came later it will look like it is not met. When the conditions don’t work as fast as they are supposed to, it is necessary to do actions and review formulas to manage the screens in which formulas run.

How Should the Formulas Be Managed?

  • An evaluation screen should be created for the formulas.
  • Like we always mention, it is very important to pay attention to naming correctly. The Label in which the formula is evaluated should be named based-on what it does and the outcome it creates.
  • The formula should produce 1 and  as a result when it is used with the If function.
  • Since actions like OnSelect, OnVisible,OnScan are instant actions, the formulas that has a long duration should be calculated outside with labels and be run with 1,0 control only.

Text Functions

Power Apps Writing Formulas

PowerApps has its own software language and its special functions. Alongside these functions, it also contains a lot of functions that exist in Excel. These functions are offered in a general frame, they are not separated into categories as function groups like text, date, and statistic. In this article, we will analyze text functions.

It is very practical to code with PowerApps, with the formula language you are used to from Excel.

You can write all formulas on objects. After you choose the related object, you can write on Formula Bar.

*While writing a formula, you can write with commas (,) or semicolons (;) (depending on the browser language), you don’t need to use the equal sign.

Text Functions

There are basic text functions like Concatenate, Left, Right, Mid, Len, Upper, Lower in the Power Apps functions. You can edit or create text expressions with these functions.

Let’s write the formulas below for a textbox in which Text Functions is written. The name of this textbox is txt_sampletext.

The Concatenate function concatenates a mix of individual strings and a single-column table of strings. When you use this function with individual strings, it’s equivalent to using the & operator.

ConcatenateString1 [, String2, …] )

Concatenate(txt_sampletext.Text;  txt_sampletext.Text;  txt_sampletext.Text)

 

Left function returns the beginning characters of a string.

Left( StringNumberOfCharacters )

Left(txt_sampletext.Text;1)

 

 

Mid function returns the middle characters of a string.

Mid( StringStartingPosition [, NumberOfCharacters ] )

Mid(txt_sampletext.Text;3;1)

 

 

Right returns the ending characters of a string.

Right( StringNumberOfCharacters )

Right(txt_sampletext.Text;1)

 

 

Len function returns the length of a string of text.

Len( String )

Len(txt_sampletext.Text)

 

 

Lower function converts any uppercase letters to lowercase.

Lower(txt_sampletext.Text)

 

 

Upper function converts any lowercase letters to uppercase.

Upper(txt_sampletext.Text)

 

 

Proper function converts the first letter in each word to uppercase if it’s lowercase and converts any other uppercase letters to lowercase.

Proper(txt_sampletext.Text)

 

 

The Trim function removes all spaces from a string of text except for single spaces between words.

Trim(txt_sampletext.Text)

 

 

The TrimEnds function removes all spaces from the start and end of a string of text but leaves spaces between words intact.

TrimEnds(txt_sampletext.Text)

 

You can access the list of all the other functions here.

Automatic Page Redirect with Timer

Navigate Between Screens

The Navigate function is used to navigate between screens in PowerApps mobile apps. Navigation between screens can be done when the objects are touched/clicked(by the trigger of user) or within a certain period of time(with a timer). In our previous article, we navigated by writing a formula into the OnSelect property of a button. In this article, we will automatically navigate after a while.

Timer

You can use the Timer object for each action that will be executed based on a duration. This way, when the time start or ends, it will be run to be applied on the actions you’ve determined. Timer works here as a trigger (It is a word that we use a lot in our Power Automate articles). It starts for the time to be up and starts the flow.

Timer objects has properties like OnTimerStart, OnTimerEnd, Duration, Repeat, and Reset and actions are executed with these properties. You can take a look at the general features of Timer here.

For example, you can display a text and image that states uploading/saving/wait etc. You can start the page redirect when it’s OnTimerEnd.

When it’s OnTimerStart and OnTimerEnd, you can create a collection. You can create or compare conditions.

Navigate

Let’s make an example where we see a screen with the “Saved successfully” text after saving and when the duration is done we go back to the main page.

Two screens need to be added into the application. Let’s call one of the screens SCR_Mainpage , and SCR_Action_Successful the other.

Timer object will be added to the SCR_Action_Successful page.

Duration property of the Timer objects writes in milliseconds, and this indicates the duration of the times. When you add a timer, standard Duration comes as 60 000 and it indicated 60 seconds. Which means that when the timer start from 1 and end with 60, it will start the actions.

Let’s make the Duration 3000 and get the action done in 3 seconds in order not to wait for too long and to see the results faster.

Duration: 3000

When the specified time of 3 seconds ends, we’ll be redirected to the main page.

OnTimerEnd: Navigate(SCR_Mainpage)

It was set as true in order for the timer to start automatically.

AutoStart: true

After these actions, the timer works actively. If you want, you can hide the time by setting the Visible property as Visible.

Use Objects with a Condition in Gallery

We can design lists with a condition in the gallery by writing the If formula. With these conditions, it is possible to control a lot of features of objects like color, view, size, position. You can authorize based on person/department, hide/show buttons on the screen and highlight status in case of tracking.

Use Objects with a Condition in the Gallery

We will be going over how to show different objects for different question types in the gallery. This way, one question in the same group will be replied with the radio button and the other will be replied with rating.

Create a Collection

We will create a sample collection for an example with the data above. You need to create the collection by writing the code below into the OnSelect property of a button we will add to the screen.

ClearCollect( Questions;
{
Question:    “Question”;
Question_no:   “1”;
Question_type:    “Radio”   }
;
{
Question:    “Question”;
Question_no:   “2”;
Question_type:  “Rating”  }
;
{
Question:    “Question”;
Question_no:   “3”;
Question_type:    “Slider”   }
)

Show Objects in the Gallery

You need to add the Gallery object to the screen in order to do the action in the example. Questions collection should be connected to this gallery object’s Items.

The responding way of each question in the collection is indicated in the Question_type column. Depending on the responding way of questions, Radio Button, Rating and Slider has been added to the gallery. IF formulas will be written into the Visible property of each object and thus, the related item will be shown depending on the question type and the other ones will be hidden.

 

Radio Button Visible Code: If(ThisItem.Question_type=”Radio”;true;false)

Rating Visible Code: If(ThisItem.Question_type=”Rating”;true;false)

Slider Visible Code: If(ThisItem.Question_type=”Slider”;true;false)

 

 

You can click here to take a look at other Power Apps articles.

You can click here to take a look at the use of IF function.

 

GUID Function

What is the GUID Function?

A random id in all screens is created with the GUID function every moment. ID values are used as a key by the database systems like Common Data Service and SQL Server.

When the function is used on its own, it can include numbers, lower and upper case and hyphen. As you can see, this function returns pretty long outcomes but this function can be managed with some functions.

guid

GUID returns a different value each time the function is calculated.  If nothing else changes in the formula, it will have the same value throughout the execution of your app.

GUID is a volatile function when used without an argument. You can write it into a label in order to view or change the outcome of the function. You can convey it to the argument for the outcome to change actively.

A random id can be created while changing the page, opening the app, and saving data or with the timer.

 

GUIDE Function and Examples

Collect

For example, you can convey this function to a certain column by creating a collection.

Collect(Table1; {   Guid_Columns: GUID()  } )

Mid

GUID function creates an outcome that includes numbers, lower and uppercase and hyphen. For example, if you want to produce a 5-character outcome, you can use the Mid function.

When you add a label to the screen an set its Text property as Mid(GUID(); 1 ;5), a 5 character GUID is created.

Set

You can use the SET argument when you need to create a new argument id all the time.

Set the OnSelect property of a button you’ll add to the screen as Set(Guid_create ; Mid(GUID(); 1 ;5)) and the Text property of the label you’ve just added as Guid_create. Now each time you click the button, a new value will be created and it will be see in the  label.

 

Click here for the general usage of the GUID() function.

You can access the other Power Apps articles here.

Global Set Variable

Each object or property we use in Power Apps can be used as a variable. For example, we can set the value of a TextBox as TextBox1.Text and this way, it is defined as a variable that has a text value and that can be interfered by the users. Even though every object can be used as a variable, there are real functions designed to be a variable. These functions that are experts in assigning an using variables are used in more dynamic actions like calculating a value in an increasing or decreasing way, rather than a static Label moving data between pages. Set variable is one of them.

Properties

  • It’s a global/general variable, can be applied from all the screens of the app.
  • It can be created and applied anywhere in the app.
  • It can hold different data types like Numbers, Text string, Boole, Record and Table.
  • Set(variable_name; value)

Set Variable

It is a very easy-to-use function. You define the variable name and state the value that will be written in it. And then, the variable name is written in the required field and the data it contains is open to use.

Let’s practice with a counter example by creating a variable.

Add a TextBox and two buttons into the screen and position them like below.

Write + and – on the buttons. Set() functions will be written in the OnSelect property of buttons.

+ button: Set( Counter; Counter+1)

With this formula, the last value of the counter is increased by 1 and the data is transmitted to the counter again.

– button: Set( Counter; Counter-1)

With this formula, the last value of the counter is decreased by 1 and the data is transmitted to the counter again.

Label: Counter

Name of the variable is written in to the Text property of the Label and the value the variable holds is reflected on the screen.

Click here for the general usage of the function.

You can access the other Power Apps articles here.

Variable Types

Each object or property we use in Power Apps can be used as a variable. For example, we can set the value of a TextBox as TextBox1.Text and this way, it is defined as a variable that has a text value and that can be interfered by the users. All the values like the color of an object, the text, borders can be used. From this point of view, we can see all the objects as variables. You can take a look here to read other article concerning this topic.

Even though every object can be used as a variable, there are real functions designed to be a variable. These functions that are experts in assigning an using variables are analyzed under 3 categories for Power Apps. They are used in more dynamic actions like calculating a value in an increasing or decreasing way, rather than a static Label moving data between pages.

Power Apps Variable Types

1-General Variables:

Set

  • It’s a global/general variable, can be applied from all the screens of the app.
  • It can be created and applied anywhere in the app.
  • It can hold different data types like Numbers, Text string, Boole, Record and Table.
  • Set(variable_name; value)

2-Context Variables:

UpdateContext

  • they can be applied from one screen only.
  • a single value, a record, a table, an object reference, any result from a formula
  • UpdateContext({variable_name : value })

3-Collection: 

Collect & ClearCollect

  • Holds tables that can be created and applied anywhere in the applications.
  • Saved in the local device to be used later.
  • Different data can be enter based on columns or rows in a table.
  • Every time collect is created, writes the data on top of each other.
  • ClearCollect deletes an existing table and recreates it from scratch every time.
  • Collect( table name ;{column1: “value1” ; column2: “value2”; column3: “value3” ;…})
  • Collect( table name ;{column1: “value1” ; column2: “value2”}; {column1: “value1” ; column3: “value3”} ; {…})

You can click here to take a look at the other article concerning the usage of collections.

Authorization with Department Control

You can enable interactive flow or do visual actions by creating rules in the application screen. Depending on the situation in the application scenario, you can connect objects or actions to department, user mail, location, title, marital status or user choice in multiple-choice actions.

Usually hide, show, freeze, open to edit or change color, location, size actions are done for the determined objects. You can apply all these controls in all application scenarios easily and flexibly.

Users whose department is HR will see the HR button that enables them to open the management and confirmation screen of HR.

It is better to do these actions step by step and in pieces while doing these controls. Like I mentioned in my previous article, the formula evaluations should be controlled in different labels and should be named depending on the outcome.

We will test the user department first in the example below.

Department Detection for the Existing User

We will use the Office365Users.MyProfile() formula that lets us get the existing login info of the user that opens the app for this action.

We will write the formula below in a label we named lbl_user_departmant and get the department information.

Office365Users.MyProfile().Department 

Is The Detected Department HR?

We will write the formula below into a label we named lbl_is_user_department_hr and check if the existing user is in the Human Resources department. If the user is in the HR, we will get the outcome as 1, if not; 0.

 

If the existing user is HR, what actions will be on the screen?

The Visible option of HR button will change depending on the 1/0 values that will come from the label named lbl_is_user_department_hr.

This way, the button will be showed and hidden depending on the department.

If(lbl_is_user_department_hr.Text=Value(1);true;false) 

Control Formulas

The labels named lbl_user_departmant and lbl_is_user_department_hr should be moved to the formula screen so that it is easier to revise them later. Click here to take a look at the article about creating a formula screen and its importance.

You can read about the details of the functions that are used to get user information through Office 365 here.

Similar Examples

You can do similar control with the same steps while filling a form prepared for personnel that consists of personal information like Name, Surname, ID Number, Gender, Date of Birth, Marital Status, Military Service Status.

When the gender is marked as Female, the military service status will be hidden (Visible) since it won’t be filled or it can be frozen (DisplayMode).

When the marital status is marked as single, the fields concerning spouse and children can be hidden (Visible) since they won’t be filled or they can be frozen (DisplayMode).

You can click here for other Power Apps articles.