Strengthen Your Communication with Sway

You can directly access your personnel and customers with Ms Sway. You can create and present your journals, announcements or other communication posts with a rich content.

You can use SWAY as a communication channel where communication is in the forefront. Sway can be viewed on all mobile devices like cell phone, and tablets.

You can easily add images, videos and other multimedia objects. You can customize completely and strengthen you communication with rich images. You can use Web site connections within Sway.

On top of that, you can add your pools and tests on MS Forms or Power BI reports into Sway with Embedded code.

With available templates, it is very easy to get started with Sway. You can access the Sway journal where you can see how to do it here.

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.

Concatenate( String1 [, String2, …] )

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

 

Left function returns the beginning characters of a string.

Left( String, NumberOfCharacters )

Left(txt_sampletext.Text;1)

 

 

Mid function returns the middle characters of a string.

Mid( String, StartingPosition [, NumberOfCharacters ] )

Mid(txt_sampletext.Text;3;1)

 

 

Right returns the ending characters of a string.

Right( String, NumberOfCharacters )

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.

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.

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.

 

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.

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.

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.

FIELDVALUE Function

In this article, we will be informing you about the FIELDVALUE Function used with the Linked Data Types. You can use the FIELDVALUE function to retrieve field data from linked data types like the Stocks or Geography data types.

bağlantılı veri türleri

There are easier methods for writing formulas that reference data types, so the FIELDVALUE function should be used mainly for creating conditional calculations based on linked data types.

 

SYNTAX

fieldvalue fonksiyonu söz dizimi

This function requires two arguments.
It is necessary to enter both of these arguments.

There are these independent variables in the syntax of the FIELDVALUE function:

  • value– Function returns all matching fields(s) from the linked data type specified in the value argument.
  • field_name– The name or names of the fields you would like to extract from the linked data type.

Description

  • The FIELDVALUE function returns all matching fields(s) from the linked data type specified in the value argument.

  • The FIELDVALUE function belongs to the Lookup & Reference family of functions.

 

Examples

In the following basic example, the formula =FIELDVALUE(A2,”Area”) extracts the Area field from the geography data type for Turkey.

fieldvalue fonksiyonu örnek

Let’s list the data in the related field depending on the dynamic field choice about cities.

fieldvalue fonksiyonu örnek

 

Remarks

If you try to retrieve data from a non-existent data type field, the FIELDVALUE function will return the #FIELD! error. For instance, you might have entered “Field“, when the actual data type field is named “Area“. Double-check your formula to make sure you’re using a valid field name. If you want to display a list of field names for a record, select the cell for the record, and press Ctrl+Shift+F2 .

You can get more information on Microsoft Support.

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

Office Insider – What Happened in June?


NEW FEATURES/FIXES

 

You can find and follow all monthly Office insider new features and fixes (updates) on our blog. 👍🏻  Officer insider gets updates and new features regularly. It is important to follow these Office insider updates and use them in terms of increasing your knowledge. Now let’s take a look at what kind of changes happened in Office insider in the month of June.

 

 

June 06, 2020

 

excel new iconExcel

Sheet View

Now you can sort and filter your Excel file while collaborating with others with Sheet View.
Thanks to this new feature, users working on the file together don’t get affected by the others’ sorting and filters.

 

Create PivotTables from Datasets in Power BI

Now, you can create PivotTables connected to datasets stored in Power BI in Excel with just a few clicks. This allows you to get the best of both PivotTables and Power BI.
Calculate, summarize, and analyze your data with PivotTables from your secure Power BI datasets.

Learn More

Notable fixes

  • An issue that caused the custom values in the chart axis not to be applied correctly has been fixed.
  • An issue that caused saving worksheets containing multiple formulas with defined names to take more time compared to now.

outlook new iconOutlook

Quickly reopen items from previous session

We added an option to quickly reopen items from your previous session. Now you can automatically restore items that were open when Outlook got locked or closed.
To turn off the feature go to Options > General > Start up options.

önceki oturumdaki öğeleri yeniden açma

Notable Fixes

  • An issue where the Input Method Editor (IME) window would overlap the underlying text being entered via the IME when using multiple monitors with different resolutions has been fixed.
  • An issue where viewing a template when composing a new email message would result in a crash has been fixed.
  • An issue where users were unable to Exchange 2010 public folders after Outlook version 1911 has been fixed.
  • An issue where the Categorize button for group calendars in the Office Ribbon was disabled has been fixed.
  • An issue that caused users to experience a crash in Outlook when working with hyperlinks in plain text emails has been fixed.
  • An issue that caused Outlook to be unable to parse long file names encoded with RFC2231 has been fixed.
  • An issue that was causing Outlook users to experience intermittent hangs when using screen readers has been fixed.
  • An issue that resulted in the Online Archive dropdown in folder properties to be missing for users on high DPI monitors has been fixed.
  • An issue that would cause users with conflicting contacts to experience crashes in Outlook has been fixed.

word new iconWord

Notable fixes

  • An issue where timestamps in Comment panes were not based on the system locale time has been fixed.
  • An issue where comments between the web app and the desktop application were not in sync has been fixed.

PowerPoint

Notable Fixes

  • We fixed an issue where the slide editor from one slide would overlap on to the next slide.
  • We fixed an issue where a Comment pane that had been closed by the user would re-open automatically.
  • We fixed an issue where PowerPoint files with embedded charts/workbooks could result in failures when saving the file.
  • We fixed an issue with opening files from server-configured with Forms-based authentication.

msproject new iconProject

Notable Fixes

  • We fixed an issue that prevented orphaned tasks from being deleted or reassigned after the parent plan was deleted.

 

June 12, 2020

excel new iconExcel

Get Organization Data from Power BI using Data Types

Excel data types from Power BI are now rolling out to Insiders in organizations with Office 365 E5/A5 or Microsoft 365 E5/A5. Getting the information you need and easily refreshing it is critical to many everyday workflows.  You can access your company or organization information from Power BI as a data type in Excel, which expands your ability to bring in linked information in your spreadsheets.

Learn More 

Notable Fixes

  • An issue where the major gridlines of radar charts could not be formatted correctly has been fixed.

word new iconWord

Notable Fixes

  • An issue where the ability to clear formatting within the Comments pane via the Clear Formatting button in the Office Ribbon was not working has been fixed.
  • An issue where changing the size of a table when the ruler is not displayed caused other applications running in the background to start flashing has been fixed.
  • An issue where if Word had a list of more than 50 frequently opened documents, then after saving and opening a document, a revision history would be displayed even though no revisions were made to that document has been fixed.

msproject new iconProject

Notable Fixes

  • An issue where a baseline reset or update could change time-phased budget cost/work resources and the baseline could reflect incorrect budget values has been fixed.
  • An issue where the ProjectBeforeTaskChange event didn’t fire when there was a change to the project summary task, either the project start/task field has been fixed.

Access

Notable Fixes

  • An issue that caused Microsoft Access to fail to identify an Identity Column in a linked SQL Server table, which could cause rows to be reported as deleted incorrectly has been fixed.

June 19, 2020

 

excel new iconExcel

Notable Fixes

  • An issue where workbooks were read-only when the file only had read-only recommended has been fixed.
  • An issue where CustomUI XML for a custom ribbon tab was removed when saving to SharePoint /OneDrive has been fixed.

PowerPoint

Notable Fixes

  • An issue where a user’s presence color indicator was not getting refreshed in the co-authoring gallery during a live, co-authoring session has been fixed.

word new iconWord

Notable Fixes

  • An issue where the HTML hyperlink color was not being rendered correctly has been fixed.

outlook new iconOutlook

Notable Fixes

  • An issue where the Input Method Editor window would overlap the underlying text being entered via the IME when using multiple monitors with different resolutions has been fixed.
  • An issue where the dates in mini calendar failed to display in bold for users in Japan has been fixed.
  • An issue that prevented calendar reminders from showing exact times for the meetings coming up in less than a week has been fixed.
  • An issue that caused users to see the following error when closing an appointment that was previously saved has been fixed: “The item cannot be saved because it was changed by another user or in another window. Do you want to make a copy in the default folder for the item?”

msproject new iconProject

Notable Fixes

  • An issue where if Fixed Duration tasks are%100 complete but Actual Finish is not specified, the Task % Complete would display as less than 100% has been fixed.

 

Office Suite

Notable Fixes

  • An issue where the URLs that were not http or http based were not being displayed in the Most Recently Used list hast been fixed.

 

We compiled all the new features and fixes in June in Office insider. Hope to see you in our other articles, bye bye. 🙋🏻‍♂️
You can share this article with your friends and family to help them get information about Office insider updates released in the month of June. 👍🏻