Microsoft Forms - HELP

(0) Share

Report

Posted on by Community Power Pla...

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

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

R3dKap 1,594 on at

Like (0)

Report

Hi @Anonymous,

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?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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).

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.

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.

Was this reply helpful? Yes No
R3dKap 1,594 on at

Like (0)

Report
Great @Anonymous, 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...

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
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...

This is what it looks like when errors shown.
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})

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)

I have resolved the accessibility errors.

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

Was this reply helpful? Yes No
R3dKap 1,594 on at

Like (0)

Report
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...

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (0)

Report
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.

❗❗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"

Was this reply helpful? Yes No
R3dKap 1,594 on at

Like (0)

Report

Ok, nice !

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

What's in these properties?

Form1.DataSource

Form1.Item

Form1.DefaultMode

SubmitButton.OnSelect

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No
R3dKap 1,594 on at

Like (0)

Report
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?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

I get this error if i click 'play' from the list of PowerApps via logging in (instead of clicking edit).

I then enter the details and press submit and i get this..

I also do not get any notification whether it was successful or not However...

If i 'edit' my app then press play and fill out details then press submit it comes up with the error check notification - as success.

No idea why it keeps coming up with that second page or if its i need to do an refresh script when submit button clicked.

I've checked my file and the input is getting added right at the bottom of the table (my bad)

The sitelocation & telenumber selected is not getting added can you help with this please.

Is it not possible to get this data at the top?

Was this reply helpful? Yes No