sql help

[shredded]

Trying to pull data (list of DataIDs) where they have null Notes, see image.

I want to see the ids from the related layout table Master.

Any help in the right direction will be great. thanks.

[bcooney]

If I understand correctly, you want a list of master records that do not have notes? SQL can be slow with joins.  Find all records  in the join table and  use go to related from the join table up to master, match found set and then show omitted. This will leave you with all master records that do not have a join record, and therefore notes. 

[shredded]

While on the layout (Master), I'd like to have a field like Master::EmptyDataIDs, which is an sql that shows all __DataIDs related to Master, but with empty Notes field.

Edited August 1, 2018 by shredded

[bcooney]

I never create a calc that has SQL in  Define Fields.

So, what you're saying is the the Data table has a field "Note" that can be empty. I would create a new field in Data MissingNoteInd = is empty ( Notefield)

Then build a new relationship from the join table to Data

MasterJoin::fK_dataId = __DataId and

MasterJoin::ONE = MissingNoteInd

ONE is a field we have in every table. It's very useful for filtering relationships.

ONE = Let ( triggerOnChangesTo = Self ; 1 ), auto-entered calc, number, always evaluate

 

[Fitch]

I agree -- it's generally a Bad Idea to put SQL in calculated field definitions, or tooltips, conditional formatting or any other "static" elements.  It will kill performance unless you are very very sure of what you're doing. Use SQL only for scripts.