If you upgrade to one of the more recent versions of TMS,  the SQL views
are automatically generated,  making it easy to include Flex Fields in list
views and Crystal Reports.

Chad

On May 5, 2017 9:15 AM, "Smith, Jeffrey" <[log in to unmask]> wrote:

> Ariane,
>
>
>
> I just recently did this so I could start building List Views with Flex
> Fields in the Loans and Exhibitions modules, so I am happy to share.
>
>
>
> First, you need to create a SQL view of the Flex Fields that are
> associated with the relevant ID: in this case ObjectID. For Loans it would
> be LoanID, loan objects LoanObjXrefID, etc. The ContextID must change
> depending on what you are pulling. ContextID = 1 for object data.
>
>
>
> --OBJECT FLEXFIELD VIEW FOR DESCRIPTIVE DATA (
> ObjectUserFieldXrefsProvDescrDataView)
>
> SELECT     TOP (100) PERCENT dbo.UserFieldXrefs.ID,
> dbo.UserFields.UserFieldName, dbo.UserFieldXrefs.FieldValue
>
> FROM         dbo.UserFields INNER JOIN
>
>                       dbo.UserFieldXrefs ON dbo.UserFields.UserFieldID =
> dbo.UserFieldXrefs.UserFieldID RIGHT OUTER JOIN
>
>                       dbo.Objects ON dbo.UserFieldXrefs.ID =
> dbo.Objects.ObjectID
>
> WHERE     (dbo.UserFieldXrefs.ContextID = 1) AND
> (dbo.UserFields.UserFieldID IN ('21', '22'))
>
> ORDER BY dbo.UserFieldXrefs.ID, dbo.UserFields.UserFieldID
>
>
>
> Then, you need to create a view that uses a variation on the pivot table
> technique to take the rows in the previous view and turn them into columns.
> Pivot tables are usually meant to do this AND sum values, so I had to find
> an alternative method. This uses MIN and works just fine:
>
>
>
> --PIVOT TABLE VIEW USING THE OBJECT FLEXFIELDVIEW
>
> SELECT     TOP (100) PERCENT ID, MIN(CASE UserFieldName WHEN 'Provenance
> editing' THEN FieldValue END) AS Provenance_editing,
>
>                       MIN(CASE UserFieldName WHEN 'Descriptive data' THEN
> FieldValue END) AS Descriptive_data
>
> FROM         dbo.ObjectUserFieldXrefsProvDescrDataView
>
> GROUP BY ID
>
> ORDER BY ID
>
>
>
> Then you create the actual List View view and use this view to pull IN the
> Flex Field data with whatever else you want in your final List View.
>
>
>
> Hope that helps.
>
>
>
>
>
> *From:* The Museum System (TMS) Users [mailto:[log in to unmask]]
> *On Behalf Of *Bélanger Ariane
> *Sent:* Friday, May 05, 2017 8:27 AM
> *To:* [log in to unmask]
> *Subject:* Adding Flex Fields to ListViews
>
>
>
> [image: Bibliothèque et Archives nationales du Québec]
>
> Hello ladies and gents,
>
>
>
> I was wondering if it was possible to add Felx Fields to ListViews? I've
> done a few tries and can't seem to find the way around it. We're running
> TMS2010 and are quite in the early stages of developping our use of it.
>
>
>
> Thank you!
>
>
>
> *Ariane Bélanger*
>
> Technicienne en muséologie, Service de la conservation des collections
> patrimoniales
>
> Direction du dépôt légal et de la conservation des collections
> patrimoniales
>
> T : 514 873-1101 <(514)%20873-1101> poste 3632
>
> [log in to unmask]
>
> BAnQ Rosemont-La Petite-Patrie
>
> 2275, rue Holt
>
> Montréal (Québec)  H2G 3H1
>
> www.banq.qc.ca
>
>
>
>
>
> [image: Icône BAnQ] <http://www.banq.qc.ca>  [image: Icône Facebook]
> <http://www.facebook.com/pages/Bibliotheque-et-Archives-nationales-du-Quebec-Grande-Bibliotheque/239106374115>
>   [image: Icône Twitter] <http://twitter.com/_BAnQ>  [image: Icône
> YouTube] <http://www.youtube.com/user/BAnQweb20>  [image: Icône
> Historypin] <https://www.historypin.org/channels/view/41410>
>
> [image: Campagne Marguerite de la Fondation de BAnQ]
> <https://fondation.banq.qc.ca/contribuer/marguerite/>
>
> *BAnQ-Avis de confidentialité*
> Ce courriel est une communication confidentielle et l'information qu'il
> contient est réservée à l'usage exclusif du destinataire. Si vous n'êtes
> pas le destinataire visé, vous n'avez aucun droit d’utiliser cette
> information, de la copier, de la distribuer ou de la diffuser. Si cette
> communication vous a été transmise par erreur, veuillez la détruire et nous
> en aviser immédiatement par courriel.
>
> 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.
>

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.