Formula to Pull a single record field from another table.

Question

I am trying to build a formula that pulls a single record via a unique field, I want to pull just a single field regardless if there are several records that match my search query. My current formula's I am trying are;
GetFieldValues(GetRecordByUniqueField([Record ID#], 7, [_DB_Alias]), 6) this formula fails as it sees a textlist from my understanding that is multiple records instead of a single record,
&nbsp;GetFieldValues(GetRecords("{7.EX.[Record ID#]}&amp;options=num-1",[_DB_Alias]),6) this fails for the same reason as I am pulling multiple records via the GetRecords function but applied the limit of num-1 as an option.
Neither work and I would like to pull only a single record with a single field back using either of these functions.------------------------------Colin Burns------------------------------

chayceduncan · Answer

Formula queries don't accept that kind of parameter to only bring back one record at this time. Your best best is to get the results you want and then do a Part([Res], 1, ";") to get the first occurrence in the response.

------------------------------
Chayce Duncan
------------------------------

colinburns · Answer

even if I changed the formula on top? A big reason why it fails is the relationship allows multiple records in this case Notes that are tied to the same record.

------------------------------
Colin Burns
------------------------------

chayceduncan · Answer

The formula on top I imagine is using the Related value in your child record so it's still returning multiple matches I would imagine. The unique field pertains to your parent - not the children. Do you have a direct relationship between these tables? Can you just use a summary field to get the Max Record ID# of your child table and then use the formula query to target that specific record?

------------------------------
Chayce Duncan
------------------------------

colinburns · Answer

The Parent record will be a single record, the child records can be multiple. The child records are tied in the relationship via the record id of the parent. As for the max record ID there will be multiple parent records with child record(s), would the max still apply?------------------------------Colin Burns------------------------------

chayceduncan · Answer

If your notes table is the child then yes, the max record ID will be the max Note Record ID of the notes related to that parent, that's the design of the relationship. So do the Max Record ID# of the notes and you have the RID of the most recent Notes entry that you can then point your formula query to.&nbsp;------------------------------Chayce Duncan------------------------------

Forum Discussion

Formula to Pull a single record field from another table.