ContributionsMost RecentMost LikesSolutionsRe: UserRolesI was hoping this was solved or we were given a new function that I did not know about since the last post. Its not something that will break my app but it would have been a nice to have. Thanks for the response Mark. ------------------------------ John Arniotis ------------------------------ UserRolesGood Morning, Does anyone know how QB evaluates a logged in users role? I want to see if the process can be broken down so that I can recreate portions of it in a formula for what I am trying to do. I know QB has a UserRoles() function but that looks for the role of the logged in user. What I am trying to do is pull a role out of a user field for evaluation on an IF statement. Based on the result the field(s) will prefill the expected information without user interaction. There was a question I found on here that is 2 years old and it did not seem possible then but I am wondering if this is possible now. (3/17/20 reply) https://community.quickbase.com/communities/community-home/digestviewer/viewthread?MessageKey=303cffc8-acce-46f7-bbb0-88f5be095313&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer Thanks, John A. ------------------------------ John Arniotis ------------------------------ KPI Formula Queries.Good morning, I posted this question before in another form but did not receive any responses. I am not sure if that is because the question was not understood or there is no known solution. I am trying to create a "score" report based on quality data entered by our QA consultants. The first issue I run into is QB seems to be taking the average of an average since I created a formula field to do the math I need for the scoring. If I add that field to a gauge report summarized by average I get the incorrect "score". I then decided to try a basic formula query which gives me the "score" I need but since its so basic it queries all of the records. I know I can add additional variables to look at a smaller subset of the data but is there a way to use user defined filters for formula queries? Ex: If I set the gauge report to display the formula query field and add that report to a dashboard is there a way to have the dashboard filters impact what data the formula query reports back? I have been able to filter formula queries for another use case, on a form, to provide names of people that have taken a class between 2 dates but I can not seem to figure out how to make it work for a report dashboard view. Any help is appreciated. ------------------------------ John Arniotis ------------------------------ Formula Query with filtersGood Afternoon, I am looking to come up with a formula query, in a formula numbers field, that can calculate a score but also react to filters when placed on a KPI (gauge) report or dashboard. I created a simple query that gives me the correct value but if I add field references and in this case they refer to each other, waiting for input from the user the calculations are off. Is it possible to add references to the query below that would allow a user to edit it on a report? I am basically looking to recreate a version of SUMPRODUCT from Excel. (I know that I could have done this differently but with the order of operations in QB this is what worked from an expected value standpoint) var number INVYES = Size(GetRecords("{31.EX.'Yes'}")); var number INVNO = Size(GetRecords("{31.EX.'No'}")); var number POLYYES = Size(GetRecords("{32.EX.'Yes'}")); var number POLYNO = Size(GetRecords("{32.EX.'No'}")); var number RORYES = Size(GetRecords("{82.EX.'Yes'}")); var number RORNO = Size(GetRecords("{82.EX.'No'}")); var number TotalYES = $INVYES+$POLYYES+$RORYES; var number TotalNO = $INVNO+$POLYNO+$RORNO; var number TotalMinus = $TotalYes-$TotalNo; var number TotalPlus = $TotalYes+$TotalNo; $totalminus/$totalplus ------------------------------ John Arniotis ------------------------------ Dynamic lists with user interaction.Hello All, I have a use case that I have been working on for a few days and cant get it to work exactly how I would like, hopefully someone here has figured this out. I have a mentee table with a child to parent relationship to a mentor table. An employee will select a skill or career from the mentee table which will show a list of names of mentors that can mentor them on that skill or career through a report link. That all works well but what I am trying to do is once the mentee selects a mentor I want to have another field where the mentee has access to the same list of names in the report link in a drop down so they can indicate on the form who they selected. A regular user field is causing problems as employees are selecting names of people that are not on the list. A conditional drop down did not work because there are too many variables to account for on what to show in the list. (mentor availability, authorization....) I was able to get a formula query set up to reliably give me the list I need but then I run into the 20 user list limit and can not set the field to be a shared value for me to use in a multiple choice field. Has anyone found a way to use a formula query to dynamically populate a multiple choice field that a user can interact with? ------------------------------ John Arniotis ------------------------------ Re: Uploading legacy data with conditional drop down fieldsSorry for not checking back in but that worked perfectly Mark. I am glad that I did not attempt to over engineer a solution. Thanks! ------------------------------ John Arniotis ------------------------------ Re: Uploading legacy data with conditional drop down fieldsI hadn't even considered that Mark. Thanks I will give that a try and report back. ------------------------------ John Arniotis ------------------------------ Uploading legacy data with conditional drop down fieldsHello All, This question may sound very simple but I am trying to find the most efficient way to tackle this issue and was wondering if anyone has run across this problem before. I created a table to make a process more efficient for one of our departments. Long story short they still tracked items in Excel and had multiple versions of this tracker on random machines. There are multiple states that have different needs so I created a few conditional drop down fields to clean up what each user sees when creating a record. All of that works fine when entering a new record. In order to keep users from simultaneously working in QB and Excel I wanted to "normalize" the legacy data and import it into QB. I have already made sure the data fits the new form but when I go to import the legacy data none of the conditional fields will update with the values coming in from Excel. I guess that makes sense since a child record gets added to the tables holding the conditional values when a new record is saved and a simple upload may not allow for that to happen. I am thinking I will have to create a pipeline but wanted to ask if anyone has run across this issue and come up with a solution. Currently I am testing this out with only one state, with minimal data, but if and when the broader release happens I am going to have to upload 10k plus records, so manual data entry is not an option. Thanks in advance for any help you may provide. ------------------------------ John Arniotis ------------------------------ Re: How do i get spell check on a rich text field?The only problem I see with that solution is the native browser solutions and extensions do not seem to be able to check the rich text fields. The only way any suggestions are made is if the user goes into the HTML code and that is not user friendly. Regular text fields seem to work with the browser implementation, at least it does with Chrome. ------------------------------ John Arniotis ------------------------------ Select user buttonI have 3 tables, goals, mentor and mentee. The goals table is a parent to both the mentor and mentee table. When a user enters a goal they have the option to select whether they want to be come a mentor or a mentee and based on that selection will show the add mentor or add mentee button which directs the user to the respective table / form. The goal is to compile a list of approved mentors that can be paired up with a mentee depending on skills or careers the mentee selects. I currently have all of that working with a simple report link which filters the mentor report based on concatenated fields in both tables. Once the mentee selects a mentor from the list I want to include a button that will extract the name from the mentor table and edit a selected mentor field on the mentee table. I have created a relationship between both tables with the mentor table set up as the parent but I am not really leveraging that at the moment. Since both tables have data entered by 2 different users and those records do not have to interact until this point does anyone have an idea on how to connect the 2 records so that I may pass information between them? I have tried URL buttons and automations but I can not seem to figure out how to pass the appropriate record IDs for this to work. Based on the current workflow there can only be one mentor record per employee but each employee can have multiple mentee records. FYI, the reason these two tables are separated is due to the approval process. Once the user selects a skill or career to either be mentored on or to become a mentor their Supervisor has to approve the request via an email notification with rich text buttons to edit the appropriate fields. At that point the mentor will be added to the suggested mentors list and the mentee will be given access to view the suggested mentor list filtered to their selection. ------------------------------ John Arniotis ------------------------------