Thanks, Chad. This behavior is occurring in the absence of a Record Selection formula, and only two tables are used (Loans and UserFieldXref).

 

I do not know SQL; what would the formula be to select all LoanID, whether or not they are linked to a Flex Field?

 

Thanks for your patience, and all you do to help us on the listserve.

 

Frances

 

From: The Museum System (TMS) Users [mailto:[log in to unmask]] On Behalf Of Chad Petrovay
Sent: Tuesday, April 17, 2018 7:39 AM
To: [log in to unmask]
Subject: Re: Flex Field Table Joins

 

If you use a LEFT JOIN to connect two tables and then have a Record Selection formula on a field from the table to the right, this becomes a de facto INNER JOIN. 

 

For instance, if you join Objects to ObjTitles, and then in the Record Selection formula say that ObjTitle.DisplayOrder = 1, this requires an object to HAVE a title that meets that criteria, excluding untitled objects from the report.

 

You can manage this in your Record Select formula, but first testing if the Object has a Title, then returning the DisplayOrder, otherwise providing the value you want to filter on:

 

(if isnull({ObjTitles.TitleID}) then true else {ObjTitles.DisplayOrder} = 1)

 

This will return all records that do not have a Title, but then enforces the condition on those that do have a title.

 

I hope this helps. ~Chad 

 

On Apr 11, 2018 1:31 PM, "Frances Francis" <[log in to unmask]> wrote:

I am building a thermometer bar chart to illustrate the progression of loan requests through our internal review process. Flex fields are entered into a loan record once a formal request has actively entered the pipeline, and will display as bars on the chart.

The goal is to display all rows from the Loans module - whether or not flex fields have been entered - so we can see (a) those waiting to enter the pipeline and (b) the progress of those moving through it.

Only two tables are involved at this point. The main table is Loans, and it is linked to the flex field table UserFieldXref (LoanID=ID). However, neither a left outer join nor a full join will return all the Loan records. Only those records with a flex field entered will pull. What am I doing wrong?

Frances

Frances R. Francis / Senior Registrar

HIGH MUSEUM OF ART, ATLANTA / 1280 Peachtree Street, N.E. / Atlanta, GA 30309

404-733-4480 / [log in to unmask]" target="_blank">[log in to unmask]

Visit high.org to reserve tickets or become a member today!

 

 

DISCLAIMER NOTICE: This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error, please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.

 

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.

DISCLAIMER NOTICE: This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error, please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.