HarrisonSmith
Qrew Trainee
Joined 6 years ago
User Profile
User Widgets
Contributions
Re: Convert Email Lookup Key to Text
yeah I've tried converting both and either field toText while leaving the other as is. It always comes back to that same issue of expecting a textlist, despite the email field not being a combined text or anything like that. Its just a normal email field if(toText([Primary Matter Contact - Email]) <> "", [Primary Matter Contact - Email], toText([Referring Troutman Attorney (ref) - Email Text]))22Views0likes1CommentRe: Convert Email Lookup Key to Text
Sure. When I entered what you had above, it says I can't use <> on types text or textlist. The formula field is already a formula - email field, though I've tried with both formula email and formula text to no avail. if([Primary Matter Contact - Email] <> "", [Primary Matter Contact - Email], [Referring Troutman Attorney (ref) - Email Text])25Views0likes2CommentsConvert Email Lookup Key to Text
I feel like I'm going insane this should be extremely easy. I'm trying to make a formula email field that simply gets populated with 1 email field (primary contact) if that's in fact filled out. Otherwise, populate with the referring attorney email field value. Both are lookups from two different tables. The email field for the referring attorney lookup is the key field for that table. For some reason QB is expecting a textlist, not text. I've tried all sorts of type conversions, as well as part/split functions (since Split is what comes up under Textlist Functions, though the email field would never have more than a single value in the first place). I know this is going to be ridiculously simple in the end.43Views0likes6CommentsAdd Existing Child Records from New Parent Record
I have an app where people can have many related child documents. Now, the app requirements have expanded, so that documents can belong to many people. I want users to be able to select multiple existing people records when they create a new document record and uploading the file attachment, basically like a multi-select text field but for existing soon-to-be child records. I'd prefer to avoid creating a many:many assignments table for the sake of simplicity but if I have to then I have to. Any ideas?43Views0likes1CommentRe: Sum Overlapping Time/Remove Timeframe Gaps
Well my ultimate goal is I need to categorize each person into 6 buckets: lived/worked within 2 mile radius for >= 10 years lived/worked within 2 mile radius for >= 5 years lived/worked within 3 mile radius for >= 10 years lived/worked within 3 mile radius for >= 5 years lived/worked within 4 mile radius for >= 10 years lived/worked within 4 mile radius for >= 5 years The proximity distance field is a numeric field that exists on each residence/employment record (anyone who is categorized into the 2 mile/10 year bucket will automatically fall into each subsequent bucket, etc.). My assumption was that whatever solution can work for 1 bucket can be duplicated for the others, so I would ultimately probably need 3 numeric fields (1 for each distance threshold) that specifies the amount of months each person spent at that distance, to go along with 6 formula checkboxes (1 for each category above) that look at whether each distance duration number is >= 120 (for 10 years) or >= 60 (for 5 years).15Views0likes1CommentRe: Sum Overlapping Time/Remove Timeframe Gaps
a total for all time. I have a calc start & end formula date fields that put a cap on the timeframe if the provided date ranges exceed the relevant timeframe. There are currently 100 claimants, 210 residence records, and 40 employment records, knowing that will increase in the future, but it shouldn't get into the 1000's.24Views0likes0CommentsSum Overlapping Time/Remove Timeframe Gaps
Hi all, is there a way to do this formula queries? I have a table of people who have child residence & employment records, with start & end dates for each, as well as a numeric field that summarizes each timeframe in months. I need to query for all child residence & employment records related to each person, find any potential overlapping timeframes between the 2 tables based on start & end dates, and sum the total # of months from the Duration (months) field from applicable records. Essentially I need a final number in months of time each person provided to us across 2 tables, so any overlapping time would basically get deduped out. I also need it to be smart enough to recognize some of these timeframes are not contiguous and contain gaps, so we might not have any date for particular person from 2010-2015, but we do from 2005-10 and 15-18, so any gaps would need to be excluded from the final count.83Views0likes7CommentsRe: Funnel Report that isn't mutually exclusive
Thanks Mark, the summary report idea works well enough for what I need. It's a shame there's no way to do this natively in a funnel though, it almost seems like that's sort of the entire point of a funnel report in the first place.21Views0likes0Comments