Selecting which fields to display from Formula URL searches
I have a number of Formula fields in my database which are searches of my database based on the contents of another field. The general form is this: URLRoot() & "db/" & Dbid() & "?a=QuickSearch2&srchtxt=" & ToText([<SOME FIELD>]) &"&exact=false" (This approach may be out of date now, but it was current when I built the original database in 2017). Here is the problem: The fields that get displayed are determined by which fields are designated as "This field may be used in reports." However, what I want is way to differentially control which fields are displayed, depending on which fields are being searched, whether this is done as part of the formula, or through some other mechanism. For example: Assume I have 4 content fields Category, Manufacturer, Salesperson, Location. And two "search fields" using the approach above: Search_Category: URLRoot() & "db/" & Dbid() & "?a=QuickSearch2&srchtxt=" & ToText([Category]) &"&exact=false" Search_Location: URLRoot() & "db/" & Dbid() & "?a=QuickSearch2&srchtxt=" & ToText([Location]) &"&exact=false" When I search on the contents of "Category" (I.e., looking for all records with the same product category as the current record), I want the Manufacturer field to show up as a column in the search results, but not Location. When I search by Location, I want Salesperson to show up as a column but not Manufacturer. Currently, I have to manually show/hide which fields I want to see after I do the search--very inefficient. For example, is it possible to define a table layout for search results as a form and then indicate the use of that form in the Formula?Summary Table Question
I need my Report Columns to show only one instance of a team and not 5,6,7 etc... rows where that team has executed a job. For example SPP-35 will be executing two jobs, but each job is on it's own row. The managers want to see both jobs, on a single row... is this possible in a Summary Table? Or can I do this thru a query? If so, how?
Get Record IDs from Report Link
I have three tables Templates, Template_Events & Template_SubEvents. Templates has a relationship with Template_Events, Template_Events has a relationship with Template_SubEvents, and Template_SubEvents has two self-identifying relationships (this allows for different Template_SubEvents to be selected in two places on the form). What I'm needing to do is prevent a circular dependency in the Template_SubEvents table. What I've come up with is using a Text field (Hierarchy List) to store the Child record(s) ID and the Child records of its Parent. For example: If I have Record IDs of 1, 2, 3 & 4. 2 is a Child of 1. 3 is a Child 1. 1 is a Child of 4. In the Hierarchy List for ID# 4 would be "1,2,3". In the Hierarchy List for ID# 1 would be "2,3". So ID# 4 wouldn't be able to be the Child of the records 1, 2 or 3. A formula field would compare the contents of Hierarchy List field to the selection made in the Parent_SubEvent ID field and then a Dynamic Form Rule would display a message if the formula equaled 'Y'. I'm attempting to get the Child Records from a Report Link but that is proving difficult. I have the current formula ToText(GetFieldValues(GetRecords("{228.GT.0}", "bt6eizk88"), 178)) but it's displaying the same values for ALL records. fID '228' is a summary field that counts the number of Child records. fID '178' is one of the Report Link fields. Am I on the right track for something like this or is there a better way to prevent a circular dependency? I thought about creating another table and storing Child, Parent and Grand-Parent ID but got feedback to try and make the Hierarchy List field work.
Choosing Approvers from Related Table
Hi all -- two part question here. I am trying to create a change approval flow for a complicated approval matrix. I have designed a table to pull user names for each approval "role" from a related table based on a field (region) in the record. Where I am getting hung up is that depending on the content of another field (change type) some "roles" don't need to approve. I thought I could create a dynamic form rule to delete the content in the unrequired "role" fields, but realize that I cannot change that data since it is pulling from the related table. Is there a better way to manage this matrix with multiple dependencies? Example below: The linked Table contains Multiple Regions Role--> Finance Operations Marketing Region Asia Bill Liam Anne Europe Steph Paul Maria Each Record in the Change Request table is linked to the region and has a change request type. For change type 1, I need approvers from all roles. For change type 2, I don't need Marketing. Part 2 of the question is that I would like to send email notifications only to the selected approvers. Is there a way to send a notification to the users in the names of multiple fields with one notification, or do I need 3 notifications to send to Finance, Operations, and Marketing? Thanks in advance! Chuck
