Cristina,

 

While a report formula can be used to concatenate this geography data on the fly, it’s much better to create a database view in SQL that does this for you, and then include this view in the reports.

Attached is the SQL for our concatenated view of “origin” geography, formatted from broadest to most specific geographic field.

 

It looks complicated, but all it’s really doing is making sure--for all the fields required--that each combinatory instance is covered.  In a report formula, if a condition is met that is not anticipated in the formula, Crystal Reports shows nothing – frustrating!

 

Taking the time to create such a SQL view will pay dividends down the line. It can also be used in custom list views, which is nice.

 

From: The Museum System (TMS) Users [mailto:[log in to unmask]] On Behalf Of Cristina Lichauco
Sent: Wednesday, September 03, 2014 12:32 PM
To: [log in to unmask]
Subject: Object List with Geography fields

 

We've just converted to TMS 2012 and are working on customizing some of the stock TMS reports for our use.

Does anyone have an object list that includes a concatenated geography field? For example, producing a string like "India; Mysore, Karnataka state" or "Philippines; Luzon Island" from the relevant geography fields.
Our curators require this information on just about anything that is not by a known artist, and most of the canned reports lack this information.

I have tried to add this to the exiting Object List with Images report and while it works perfectly fine as long as each record in the selection has data in one or more geography fields, as soon as it hits a record with null geography it drops the record out of the report entirely.

I would love to see an existing reports that address this.

Thanks!

Cristina Lichauco
Collections Database Administrator
Asian Art Museum
200 Larkin Street
San Francisco, CA 94102
(415) 581-3676

ON VIEW NOW
Gorgeous
June 20, 2014  – September 14, 2014
http://www.asianart.org/exhibitions_index/gorgeous

SEARCH THE COLLECTION:
http://searchcollection.asianart.org/


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.