cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Community Champion
Community Champion

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?

Anonymous
Not applicable

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
Community Champion
Community Champion

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

Anonymous
Not applicable

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
Community Champion
Community Champion

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

Anonymous
Not applicable

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"

 

 

 

 

 

 

R3dKap
Community Champion
Community Champion

Ok, nice !

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

What's in these properties?

Form1.DataSource

Form1.Item

Form1.DefaultMode

SubmitButton.OnSelect

Anonymous
Not applicable

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
Community Champion
Community Champion

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

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

  It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd. at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3rd, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!       Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪🌠

Tuesday Tips: Community Ranks and YOU

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!This Week: Community Ranks--Moving from "Member" to "Community Champion"   Have you ever wondered how your fellow community members ascend the ranks within our community? What sets apart an Advocate from a Helper, or a Solution Sage from a Community Champion? In today’s #TuesdayTip, we’re unveiling the secrets and sharing tips to help YOU elevate your ranking—and why it matters to our vibrant communities. Community ranks serve as a window into a member’s role and activity. They celebrate your accomplishments and reveal whether someone has been actively contributing and assisting others. For instance, a Super User is someone who has been exceptionally helpful and engaged. Some ranks even come with special permissions, especially those related to community management. As you actively participate—whether by creating new topics, providing solutions, or earning kudos—your rank can climb. Each time you achieve a new rank, you’ll receive an email notification. Look out for the icon and rank name displayed next to your username—it’s a badge of honor! Fun fact: Your Community Engagement Team keeps an eye on these ranks, recognizing the most passionate and active community members. So shine brightly with valuable content, and you might just earn well-deserved recognition! Where can you see someone’s rank? When viewing a post, you’ll find a member’s rank to the left of their name.Click on a username to explore their profile, where their rank is prominently displayed. What about the ranks themselves? New members start as New Members, progressing to Regular Visitors, and then Frequent Visitors.Beyond that, we have a categorized system: Kudo Ranks: Earned through kudos (teal icons).Post Ranks: Based on your posts (purple icons).Solution Ranks: Reflecting your solutions (green icons).Combo Ranks: These orange icons combine kudos, solutions, and posts. The top ranks have unique names, making your journey even more exciting! So dive in, collect those kudos, share solutions, and let’s see how high you can rank! 🌟 🚀   Check out the Using the Community boards in each of the communities for more helpful information!  Power Apps, Power Automate, Copilot Studio & Power Pages

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

Top Solution Authors
Top Kudoed Authors
Users online (4,660)