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


[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 poste 3632
[log in to unmask]<mailto:[log in to unmask]>
BAnQ Rosemont-La Petite-Patrie
2275, rue Holt
Montréal (Québec)  H2G 3H1
www.banq.qc.ca<http://www.banq.qc.ca/>



[Icône BAnQ]<http://www.banq.qc.ca>  [Icône Facebook] <http://www.facebook.com/pages/Bibliotheque-et-Archives-nationales-du-Quebec-Grande-Bibliotheque/239106374115>   [Icône Twitter] <http://twitter.com/_BAnQ>   [Icône YouTube] <http://www.youtube.com/user/BAnQweb20>   [Icône Historypin] <https://www.historypin.org/channels/view/41410>

[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]<mailto:[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.