cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TechyTish
Frequent Visitor

Microsoft Forms - HELP

Hi,

 

TASK: to create a Microsoft form that populates data as a new row into an existing Excel template.

 

The form gathers this information to submit a request for a new telephone account for a staff member

 

We have a current Microsoft form which gathers the following information for the user who needs a telephone account:

- staffFirstName

- staffLastName

- staffID

- staffEmail 

- staffSiteLocation

- staffLineManager

 

The updated form will have a new option called - staffTelephoneNumber which will be a drop down.

 

Based on the site selected certain phone numbers will appear, I need it so that when a number is selected it is unavailable to be selected again and also possible to be deleted altogether. 

 

I feel like everything but the drop down characterises is doable in Microsoft Forms hence forcing me to use Power Apps based on research. 

 

Please can someone assist or create a working solution as I don't want to make it over complicated and it's ONLY a form that basically should update an excel template.

 

The template we will then use to upload to our telephony sever to BULK create telephony accounts for staff. 

 

Also need the existing flows to stay the same which emails a ticket when submitted to Zendesk. 

 

TIA

1 ACCEPTED SOLUTION

Accepted Solutions
TechyTish
Frequent Visitor

Hi, 

I've made some progress, I changed the cbx field property for both siteLocation & telephoneNumber to a drop down as it should only be 1 selection. I then added some filter script which uses 'dependent drop down' so when site is selected, it will only show the available numbers based on its area code. Feels good to get it working.

 

I now need to get it so that when 'submit' it hides that number selected - can you please help .. see working progress below.

 

Screenshot 2021-05-26 at 01.33.21.png

Screenshot 2021-05-26 at 01.33.30.png

 

The only issue I have now is, when i press the 'submit' button, the data is not added to the excel file, it should be added as a row to the Table_Requests, can you please help!

 

 

Not used this anymore as was not needed, but thank you, i did try it and the warning remained but the error was resolved.

 

numRequested="False"

 

 

 

 

 

 

View solution in original post

14 REPLIES 14
R3dKap
Super User
Super User

Hi @TechyTish,

Before I can help you I'd like to make sure to understand your need correctly...

If a user uses this form to ask for a new telephone account, why should there be a staffTelephoneNumber dropdown field to select an existing number? Is the user supposed to select its own number if he already has one, thus preventing him from submitting a new request? Is that it?

TechyTish
Frequent Visitor

Hi,

 

The telephone server has a list of numbers we can use for new telephone accounts, this list will be on the form, each staff members telephone number is unique, therefore, the drop down is needed so that a telephone number is selected and then is added to the excel spreadsheet (see below).


Screenshot 2021-05-16 at 18.28.02.png

 

The spreadsheet has the following columns: 

- staffFirstName

- staffLastName

- staffID

- staffEmail

- staffSiteLocation

- staffTelephoneNumber

 

Below is a partial screenshot of our existing form which was created using Microsoft Forms, but we cannot implement the telephony number function int he form unless we use PowerApps, so i need to replicate that form and add the SiteLocation & TelephoneNumber fields into the form.

 

Screenshot 2021-05-16 at 18.31.57.png

When the form is submitted the data captured from the form should add a row to the spreadsheet, so each selected/relevant field in the form corresponds to a column in the spreadsheet, except for the - staffLineManager as mentioned in original post - this is on the form so that when the form is submitted a ticket is generated which also puts all the data on the ticket so that the IT staff member who picks up the ticket has the line manager name if further approval is required.

 

Each site has certain types of telephone numbers, kind of like area codes. So i was thinking the question where it asks what site the user is at would list the sites as radiobutton, when site is selected the drop down will become available to pick a number. So if 1 site is selected, it will need to show a list of numbers unique to that site, as their telephone number is unique. When the form is submitted, we need the number selected to be deleted from the drop down so that when another request is made a staff member, they cant pick the same number.

 

We expect to look at the spreadsheet once or twice a week and action it all in 1 go. The IT staff member will use the spreadsheet to upload it to our telephony server to bulk create the telephone accounts, the columns in the spreadsheet are a requirement for the csv file to be uploaded successfully. 

 

Staff request the telephone accounts for other staff, usually line managers or team leaders are the requesters. They fill out the form (request) on behalf of a staff member, i.e. a line manager is requesting a telephone account for a new starter.

 

I hope that makes sense. Thank you.

R3dKap
Super User
Super User

Great @TechyTish, that really helps...

So, what I suggest is that you build an Excel file with 3 sheets:

  • REQUESTS - A sheet with a table (named Table_Requests) to store the requests for a new telephone account (has the fields you mentionned earlier: staffFirstName, staffLastName, staffID, staffEmail, staffSiteLocation, staffTelephoneNumber)
  • LOCATIONS - A sheet with a table (named Table_Locations) to list the locations and their corresponding area codes (columns could be: locLocationName, locAreaCode)
  • NUMBERS - A sheet with a table (named Table_Numbers) of available telephone numbers provided by the telephone server, along with the corresponding location (or area code) for each number plus a column to know if the number has been requested (so columns could be: numTelNumber, numAreaCode, numRequested)

Place that Excel file inside a SharePoint document library where all people who can make a request can have access to.

Create a Power Apps application based on that Excel file. In the app, proceed as follow:

  • add an edit form control (name it frmEditForm) and connect it to the Table_Requests table
  • remove the text input control from the staffSiteLocation field, replace it with a combo box control (name it cbxSiteLocation) and set its Items property to Table_Locations (set the locLocationName field as the display field)
  • remove the text input control from the staffTelephoneNumber, replace it with a combo box control (name it cbxTelephoneNumber) and set its Items property to the following code (set the numTelNumber as the display field):
Filter(Table_Numbers, numAreaCode=cbxSiteLocation.Selected.locAreaCode && numRequested=false)
  •  add a SUBMIT button and set its OnSelect property to this code:
SubmitForm(frmEditForm)
  • add the following code to your frmEditForm.OnSuccess property:
UpdateIf(Table_Numbers, numTelNumber=frmEditForm.LastSubmit.numTelNumber, {numRequested: true})

The idea here is that once a new request for a telephone number has been submitted, a True value is set for that telephone number in the numRequested column of the Table_Numbers table. This allows us to provide the cbxTelephoneNumber combo box only with the numbers that haven't been requested yet. Much easier to implement and manage.

Hope this helps...

TechyTish
Frequent Visitor

Hi,

 

I have tried this but I keep getting errors, please see below:

So this is the App so far, just want to get fundamentals working...

Screenshot 2021-05-21 at 23.37.10.png

 

This is what it looks like when errors shown.

Screenshot 2021-05-21 at 23.38.27.png

Below 4 images show the error that has occurred from implementing the below 

 

  • add the following code to your frmEditForm.OnSuccess property:
UpdateIf(Table_Numbers, numTelNumber=frmEditForm.LastSubmit.numTelNumber, {numRequested: true})

 

Screenshot 2021-05-21 at 23.38.36.pngScreenshot 2021-05-21 at 23.38.49.pngScreenshot 2021-05-21 at 23.39.12.pngScreenshot 2021-05-21 at 23.39.27.png

 

Below 2 images show errors from where you advised: 

 

  • remove the text input control from the staffTelephoneNumber, replace it with a combo box control (name it cbxTelephoneNumber) and set its Items property to the following code (set the numTelNumber as the display field):
Filter(Table_Numbers, numAreaCode=cbxSiteLocation.Selected.locAreaCode && numRequested=false)

 

Screenshot 2021-05-21 at 23.43.31.pngScreenshot 2021-05-21 at 23.44.05.png

 

I have resolved the accessibility errors.

 

Please advise on the above please, thank you for your help in advance.

R3dKap
Super User
Super User

Ok, about the first error...

Are you sure that in your Excel file, on the NUMBERS tab, in the Table_Numbers table your have a column named exactly numTelNumber?

 

About your second problem...

Inside the formula, change this:

numRequested=false

to this:

numRequested="False"

Tell me if that works for you...

TechyTish
Frequent Visitor

Hi, 

I've made some progress, I changed the cbx field property for both siteLocation & telephoneNumber to a drop down as it should only be 1 selection. I then added some filter script which uses 'dependent drop down' so when site is selected, it will only show the available numbers based on its area code. Feels good to get it working.

 

I now need to get it so that when 'submit' it hides that number selected - can you please help .. see working progress below.

 

Screenshot 2021-05-26 at 01.33.21.png

Screenshot 2021-05-26 at 01.33.30.png

 

The only issue I have now is, when i press the 'submit' button, the data is not added to the excel file, it should be added as a row to the Table_Requests, can you please help!

 

 

Not used this anymore as was not needed, but thank you, i did try it and the warning remained but the error was resolved.

 

numRequested="False"

 

 

 

 

 

 

View solution in original post

R3dKap
Super User
Super User

Ok, nice !

First, let's fix the saving of your data...

What's in these properties?

Form1.DataSource

Form1.Item

Form1.DefaultMode

SubmitButton.OnSelect

TechyTish
Frequent Visitor

Hi,

 

The form is called frmEditForm

The submit button is called btnSubmit

 

frmEditform.DataSource = Table_Requests

frmEditform.Item = blank

frmEditform.DefaultMode = FormMode.New

btnSubmit.OnSelect = SubmitForm(frmEditForm)

 

Thank you in advance

R3dKap
Super User
Super User

Looks good to me... Can you add some notification message to both OnSuccess and OnFailure events of frmEditform so we can check if everything seems fine or if something goes wrong?

frmEditform.OnSuccess = Notify("Data saved successfully!")
frmEditform.OnFailure = Notify("Error saving data!")

And then make a test saving some data and see what show up?

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (37,191)