ContributionsMost RecentMost LikesSolutionsRe: SOLUTION: Rich Text Formula field not showing in Grid EditMeredith you are my hero. ------------------------------ Andrew Nelson ------------------------------ Re: Relationship/Summary Restrictions on Date fieldsYikes. More levels of complexity on top of what was already a complex solution to (what should be) a relatively simple problem. Belt, Suspenders, and extra Suspenders to hold up my clown pants which are then themselves holding up another, smaller pair of pants. Pants that didn't fit too well in the first place. Problems I see with this are: 1) I still have to create a new summary field for each different Equipment Type, and I expect that list to get longer over time. 2) I worry that if I'm relying on automations to "fix" the relationship field, that means there will be a delay. So I can't simply abort the save and display an error message, I have to accept the new record and then try to notify the user after the fact that it was an invalid request. Before I dive into that, can you think of any other way to accomplish what I'm trying to do without having to do the whole date table setup? A way to simply count, when a reservation is created, how many other reservations share the same date and equipment type. ------------------------------ Andrew Nelson ------------------------------ Re: Relationship/Summary Restrictions on Date fieldsIt lets you create the relationship? Or it lets you add summary fields? I can create the relationship, it just won't allow any summary fields. The limits page the error message linked to states: You will be able to create a summary field only when the foreign key meets the following conditions: The field must not have any custom permissions. Any referenced fields in the formula must not have any custom permissions. The formula field only references other fields which are either scalar or formulas that follow these rules. The maximum number of text values that a combined text summary field can accumulate is 150. Only the following formula functions are supported when not referencing dates, datetimes, or users. ToText() Nz ToFormattedText that does not reference a timezone ToNumber ToBoolean Mathematical functions like log, mod, etc. Text functions like right, left, mid, etc. URLEncode Duration conversions like ToDays, ToMSeconds, etc. Note: Any function above, like ToText(), referencing a date or time will not be allowed. Which makes it clear that what I'm trying to do is not allowed. I'm just trying to understand WHY (and, more importantly, trying to find a workaround that lets me do what I'm trying to do). ------------------------------ Andrew Nelson ------------------------------ Re: Relationship/Summary Restrictions on Date fieldsNo, [Date] is a normal Date field, and [Date ID] (the reference field for the relationship) is just a Formula-Text field containing toText([Date]). It appears that because I have used a derivative of a date field as the reference field, QuickBase simply won't allow me to create ANY summary fields on the relationship. I have done this in the past, and from what I can tell it is still working where it was already implemented. But even when I try to add another summary field to THAT relationship I get this message: We are sorry -- at this time, you cannot create a summary field using a relationship where the reference field is this type of formula field. Please visit our limits page (https://XXXXX.quickbase.com/db/main?act=dohelp&page=quickbase_limits) or contact Care for more information. ------------------------------ Andrew Nelson ------------------------------ Re: Clear out all Data in a Field - without deleting the field itselfI find that the easiest way to handle this sort of thing is to just export the table as a CSV (including the Record ID) and then add a blank column in Excel and re-import it. ------------------------------ Andrew Nelson ------------------------------ Re: Calculating a start date based on current DOB/Age and a target dateAdjustMonth(AdjustYear([DOB],65),-6) You can replace the 65 with [Target Age] and -6 with -[Months Prior] as needed. ------------------------------ Andrew Nelson ------------------------------ Re: Have some Numeric formula fields and a Duration formula fieldYou just need to do toWeeks([# of weeks left on REF]) to change it from a Duration to a Numeric type. ------------------------------ Andrew Nelson ------------------------------ Relationship/Summary Restrictions on Date fields I am working on building out a system where our foremen can "schedule" certain equipment to be used for specific days. For simplicity's sake, they do not need to check out a specific piece of equipment, but rather just reserve one of a TYPE of equipment for that date. Part of this system is that I need a way to alert the user when they request a piece of equipment that has already been reserved too many times for that particular date. My intended structure was something like this: a table called "Equipment Reservations" with the following fields: Date - the date the equipment is reserved for Equipment Type - A multiple-choice Text field that lets the user select what type of equipment is being reserved Equipment Count - this is a formula-numeric field that returns how many pieces are available based on the Equipment Type field. Essentially just a case() statement and a list of quantities for each option in Equipment Type Basically, I need a way to count up how many Equipment Reservations there are that have the same Date and the same Equipment Type, and if it is more than a certain value, either a) trigger a checkbox field on the reservation to highlight the request as invalid, or b) just halt the request and not allow it to be submitted at all. In the past, I've solved this problem in a somewhat roundabout way. I created a table called "Calendar Date" and just pre-populated it with a record for every date for the next 20 years. I use the short date text as the unique identifier and then created a relationship between the Equipment Reservations table and the Calendar Date table, using a formula-text field that simply contains "toText([Date])" as the reference field. Then I could set up Summary Fields for each Equipment Type that would count the number of Equipment Reservations related to that Calendar Date with the same Equipment Type. However, now when I go to set that up, it no longer works - Quickbase will not allow me to create ANY summary fields on this relationship because the reference field is a toText([Date]). What is the point of this restriction? Why was it implemented? Is there other any way to count the number of "siblings" that share more than one field in common? ------------------------------ Andrew Andrew ------------------------------ When the record is saved"" form rules trigger before embedded grid editsWe have an app that allows users to submit timesheets every week. The table structure is pretty simple: there is a Timesheets table that stores the timesheet itself, and then a child table of TimeEntries, which hold the individual lines on the timesheet. So, for example, a foreman could create a timesheet that contains TimeEntry lines for each member of his crew (for a single day's work), or a back office employee can create a timesheet that contains TimeEntry lines for each day worked (for a single employee). The TimeEntries are added from the Timesheet submission form with an embedded grid edit. Here's my problem: I want to set up a form rule that checks to make sure that the dates on each TimeEntry line fall within the acceptable range based on the date on the parent Timesheet. If there is a date that falls out of bounds, the rule is supposed to abort the save and display a message to the user. I set up all of the summary fields in the relationship to determine the maximum and minimum dates on the TimeEntries, and compare those to the expected max/min on the Timesheet (determined with some formula fields). The rule is all set up the way it should be, the logic checks out and it should be working, but... it doesn't trigger. What it sounds like to me is that the summary fields for the relationship don't populate until after the record is saved, or perhaps that the child records save AFTER the parent record (which I suppose makes sense - the children need to have the RecordID from the parent in order to save that info).I've considered forcing the user to save the parent record first, before entering the child records, but then we run into permission issues since most users have permission to submit timesheets but not edit them. The alternative (what we're doing now), is to use the same logic to detect these discrepancies after the fact, which works, but then the changes need to be made by payroll manually, and it doesn't have the instant feedback to employees that are making the mistakes in the first place. So the question is, how can I achieve this? Is there any way to compare the child records to the parents before the record saves if all of the records are being created at the same time? Re: How to use Most Recent Date in a Dynamic FilterI know it's been several years, but I had this exact problem and wanted to let future generations know of the easy solution. Just create a Formula-Date field, and set the formula to just equal the value of the Summary field. It mirrors the value exactly, but can also be used as a dynamic filter for reports.