Skip to main content

Limiting Return from Multiple Record Field to Single Record

  • May 21, 2020
  • 1 reply
  • 15 views
C

Hello!

I am trying to create an auto-generated field consisting of a single client name and a code assigned by me (e.g. “Client Name - Billing Code”). There can be multiple clients per billing code and currently, I supply the billing code in a separate field. My current formula is: IF({Client Last Name}="", Company,{Client Last Name}) & " - " & {Billing Code}. It is continually returning “SmithSmith - B01” rather than “Smith - B01.”

My question is whether there is a function that allows me to limit to only a single client record despite the Client field being a multiple record field? If there is no function, is there a practical workaround?

Thanks!

1 reply

Justin_Barrett
Forum|alt.badge.img+21

Welcome to the community, @Chip_Harrell! :grinning_face_with_big_eyes: This may be possible, but my question back to you is this: how would you like to choose which client’s last name to use? In your example, they’re both “Smith,” but are the last names always going to match? What if it was “Smith” and “Jones”? I’m guessing you don’t want this to be an arbitrary selection, or one set by the order that the links were made. My hunch is that one of these clients is the “primary” client, and therefore the one you want to use in this Client Name - Billing Code indicator.

If that’s the case, here’s what I suggest. In the table where these clients are recorded, add a checkbox field named {Primary}, and check that on the record of the primary client for a given multi-client relationship. Also add a formula field named something like {Primary Last Name}:

IF(Primary, {Last Name})

(change {Last Name} to point to whatever field currently contains the client’s last name)

In your main table, my gut says that your current {Client Last Name} is a rollup that pulls from the {Last Name} field in the client table. Change that to pull from {Primary Last Name} instead, using values & "" as the aggregation formula. This will leave you with a single name for each multi-client setup, which will then work in the formula you created originally.

    Terms & ConditionsAccessibility statement