ContributionsMost RecentMost LikesSolutionsRe: How to use the ToUser function Thanks Mark, you actually gave me an idea that solved my problem. My original table of names did not have emails so I created a new field and only entered the email address to those that I know are users in my app. I was then able to modify my original formula to match on email address which brought me to my goal of being able to filter reports based on the logged in user. ------------------------------ David Semitekol ------------------------------ How to use the ToUser function Does anyone know how the ToUser function works? The QB help isn't very helpful. I have a field in my table with first and last names. I would like to match those to a user list so that I can create filtered reports based on the logged in user. I created a ToUser formula field and entered a formula: ToUser([Broker Name]) While QB is not reporting any error in the formula, it is also not reporting anything in the field. Is the only way to filter a report based on the user is to create a User field? What to does the User Formula field accomplish? Thanks. ------------------------------ David Semitekol ------------------------------ Re: How to limit the number of records a user can download Okay, thanks Mark and Chayce. Yes, it's a precarious situation because there is a legitimate advantage for them being able to download some of the records to work in Excel. I don't want to take away the option completely and users still need the ability to see all records also. Too bad QuickBase doesn't have a built-in limiter on the number of records a user can download or some safeguards against that "one" malicious actor. ------------------------------ David Semitekol ------------------------------ How to limit the number of records a user can download Hello, does anyone know if we can limit the number of records a user can download? I know I can restrict all downloads, but I would like a user to be able to download a report into Excel (csv) but not be able to download every record. Or is there a way to monitor how many records a user downloads? I'm just worried about all of our data walking out the door! ------------------------------ David Semitekol ------------------------------ Re: Formula for figuring out age in months Thanks Mark, this really good and useful code that you provided! If anyone is looking for a return that is a numeric number, I adjusted Mark's code here: var date MyDate = [In Service Date]; var text Years = ToText(Year(Today()) - Year($MyDate) - If(Month(Today()) < Month($MyDate) or (Month(Today()) = Month($MyDate) and Day(Today()) < Day($MyDate)),1,0)); var text Months = If( Month(Today()) > Month($MyDate), ToText(Month(Today()) - Month($MyDate)), Month(Today()) = Month($MyDate) and Day(Today()) >= Day($MyDate), ToText(Month(Today()) - Month($MyDate)), Month(Today()) = Month($MyDate) and Day(Today()) < Day($MyDate), "11", ToText(12-(Month($MyDate) - Month(Today())))); var text RawResult= $Years & "." & $Months; If(not IsNull($MyDate), $RawResult) This is still a Formula Text field but you can create an additional Formula Numeric field and just convert with ToNumber([Age Formula Text]) ------------------------------ David Semitekol ------------------------------ Re: Address Importing Best PracticesSorry Mark, yes, the Street Name is a lookup field. I'm okay with the current solution of using a custom data rule. What I'm really struggling with is the QB Address Field and the formatting of the MapBox address. They just are not using standard address formatting that we expect from the USPS or Google Maps. This causes 2 issues: The first is that searching for addresses becomes very difficult because end users are accustomed to entering an address using the standard format. But when the QB Address field changes the way an address gets entered, the user can no longer find the address using the search records tool. The second is there is no way to prevent duplicate entries easily, if at all, so it becomes a data entry nightmare. Sure, we could write a complex formula that looks for "North" and converts to "N" but that won't account for all variables in an address. Like when "North" is actually the street name and not the predirectional. Today's end user wants to be able to start entering an address and have a dropdown autofill the result. We need to find a way to duplicate that experience in QB. I think that is why I've been experimenting with this complex address table but I'm not sure if it is future proof, provides a quality user experience, or can scale when we go from 1k to 5k to 10k plus records. Any help would be greatly appreciated in solving this Rubik's Cube. ------------------------------ David Semitekol ------------------------------ Re: Address Importing Best PracticesHi Mark, I'm referring to the "Must be unique" checkbox in the field settings. Here is my formula: List(ToText([Number Start]),ToText([Number End]),[Address Number],[Predirectional],[Street Name],[Suffix],[Post Directional]) When I tried to save the field, I got this error: When I did some research, I came across this post in the community: Enforcing Uniqueness on a Formula Field ------------------------------ David Semitekol ------------------------------ Re: Address Importing Best PracticesThanks Don, this sounds like good advice I'll have to look into it. How are your users adding individual addresses? Did you ever adopt the QB built-in address field? I'm researching the Google Address Validation API and seeing if that can be incorporated into QB. It requires learning about QB Code Pages too unfortunately. For my address duplication check I'm using 2 parts. The first is a formula field that combines all of the necessary elements of the address. The second is then a custom data rule on the table that alerts the user if they are trying to enter a duplicate address. QB won't run a duplication check on a formula field and my end users won't have the option to bulk import additional addresses. The workflow will only require them to enter a few addresses here and there. ------------------------------ David Semitekol ------------------------------ Re: Address Importing Best PracticesI've been experimenting with an address table built into the database. Here are the fields: Address Number Predirectional Street Name Suffix Post Directional City State Postal Code The Street Name and City are fields related back to their respective tables that are preloaded. Since these are constant it should help the end user when entering new addresses. The Pre & Post Directionals along with the Suffix are simple dropdown fields since there are a lot less variables. Inside the table I also have a couple of formula fields to create the full address and run a duplication check. The idea is that this table is then related throughout the database. The other main tables can then be linked together through a common address that is consistent. I'm not 100% sold on this solution though, I'm not sure if it will be too cumbersome for the end users. ------------------------------ David Semitekol ------------------------------ Re: Address Importing Best PracticesThanks @Mark Shnier (Your Quickbase Coach) for the suggestion. My main concern is how the QuickBase address field creates the addresses. They don't seem to use a standard convention that we normally see in Google Maps, or more importantly, USPS guidelines. Unfortunately, I'm importing a few thousand addresses so I'm desperately trying to avoid the human review. I'm also trying to plan for the future, for when end users enter addresses into the database. I'm trying to control accuracy and consistently of the newly entered addresses. Is it possible to plug in the Google Address Autocomplete into QuickBase? Or QuickBase, is it possible to change the formatting of your Address Autocomplete? ------------------------------ David Semitekol ------------------------------