Get Record IDs from Report Link

Question

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.

hannahjacobson · Answer

If I'm understanding the basics of what you want to do, I wonder if you could get what you need by creating a couple combined text summary fields of the record id's of related records as well as a formula field?

	Record picker field	Combined text summary of 'Record ID#s'	Combined text summary of 'Children'	Formula to compile Children & Grandchildren into a text list
Record ID#	Parent Record	Children	Grandchildren	All children
1	4	2;3
2	1
3	1
4		1	2;3	1;2;3

You could likely do something similar using a single formula query, but you'd need to add criteria to the 'get records' portion of the formula - right now looks like it's just returning fid 178 for all records on that table where the value in fid 228 > 0.

From what I'm reading it sounds like you would want to search the table for records that are related to the current record (aka children) and records related to those children (aka grandchildren).

The page I find the most when building formula queries is here, I'd highly recommend it: https://www.quickbasejunkie.com/blog/formula-query-functions

Hope some of that is helpful!

davidwendt1 · Answer

Thank you for the reply! Yes, that does help! Right now I've been able to make it work with Pipelines, but I would rather have something that will update in real-time. Again, thank you!

Forum Discussion

Get Record IDs from Report Link