AnsweredAssumed Answered

Have field equal to all possible distinct options of a related field

Question asked by madmike6537 on Aug 31, 2015
Latest reply on Sep 14, 2015 by madmike6537



I have a tough one, thinking of scripting it but figured I would ask the experts first if there is a way to do this with a calculation.


I have a field, call it fieldA. I would like fieldA from table A to be equal to the Distinct options of the data entries in fieldB in table B. I need the format to be in a text list inside the field like so:


field A Table A (all inside one field and one record)

Primary Key       Field A

1                       option1




Field B from Table B (3 records)

Record primary key   FieldB

1                               option1

2                               option2

3                               option3


I have tried using ExecuteSQL with something like this:


ExecuteSQL("SELECT DISTINCT FieldA from TABLEA"; "" &)


It returns the results in the right format, but there are duplicates, I need the distinct values. The other problem with this is that I need RELATED values, and of course ExecuteSQL doesnt automate that. I am not sure I can make my WHERE statement work to show the right related values. Was hoping for a calc to do this instead of using SQL maybe.


Any suggestions?