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
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
BAnQ Rosemont-La Petite-Patrie
2275, rue Holt
Montréal (Québec) H2G 3H1
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.
signoff TMSUSERS
// eoj
You will receive a confirmation that your subscription has been removed.