9 Replies Latest reply on Jun 22, 2016 4:56 PM by clayhendrix

    Question re: assets database

    tomeckerle

      I am creating a database of assets. There are 4 categories of assets: paintings, musical manuscripts, books and sculptures. While all assets share some characteristics, for example, creator and dimensions, each asset category also has a small number of unique characteristics like media and technique (oil on canvas,  pen and ink on paper etc.) for painting and key signature for music manuscripts. I would like to find a solution that will show all characteristics that are pertinent to each asset but not those unique characteristics that are only relevant to the other asset categories. I have created fields for all the characteristics both those that all assets share and those that are unique to each category. The type of work (4 asset categories) should trigger which specific unique characteristics (fields) are included (appear in the layout) and which are not but I don't know how to structure that distinction. Any suggestions would be most warmly appreciated.

      Thanks very much,

      Tom Eckerle.

        • 1. Re: Question re: assets database
          beverly

          Start researching SQL vs. NoSQL and you will see that FM is not the only db out there that may face these hurdles!

           

          I tend to make a design decision based on:

          • the number of records & the number of columns (per table)

          • what reports need to be used?

          • what data needs to be extracted for external exchange & does this design work for that?

          • because this is FileMaker, can I leverage FM-only features using my data model?

          • will others (eventually) need to understand the schema (tables and fields) and will it make sense to them?

           

          I might (for your assets), have a separate table for those things not common, but probably not separate tables for each type (as some purists might). The separation of common & not common makes for quicker and easier getting at the common data (for extraction & reporting), then "links" to details based on the type - different layouts or different panels (slide or tab). But that depends on the above list of design questions.

           

          beverly

          • 2. Re: Question re: assets database
            tomeckerle

            Thanks very much for your thoughtful reply. I agree that collecting the category specific characteristics in one table makes sense. I had initially considered making 4 tables - one for each category including both common and unique characteristics, but don't see how that resolves the display problem. 90% of the characteristics are common to all assets; creator, creation date, title, dimensions, location (where the physical assets are stored - one of 3 sites), condition so the unique, category-specific characteristics are a small handful 2 or 3 max for each category. I've created separate layouts for each category (duplicating the original and then swapping out fields as necessary) and that was behind my hope that there could be a trigger to switch between them based on which asset category is chosen on input (record creation), but your suggestion of different panels (tabs etc.) definitely seems like a solution.

            My limited knowledge of FM's possibilities is certainly a handicap.

            The purpose of the assets database is to give auction houses and collectors a means of viewing and evaluating works and collections of work by particular artists that are not available for physical examination. Sorting by artist, musician etc. would be an important function, but there would be no need for anyone but myself to add records, change data or interact with the db in any manner other than via search.

            Thanks very much for your suggestions, one of which will surely lead to a good solution.

            Cheers,

            Tom

            • 3. Re: Question re: assets database
              siplus

              If you look at your records one at a time, in a Form View layout, then you can have a onRecordLoad trigger which will select the correct tab from a 4-tab structure holding the specific fields unique to the record's category. I can build an example if needed.

               

              Attaching example:

              1 of 1 people found this helpful
              • 4. Re: Question re: assets database
                tomeckerle

                Ciao Sipus,

                That sounds like a great solution. If I understand what you¹re suggesting

                the characteristics that are unique to each of the four asset categories

                would be in the tabs and when the record is created the choice of tab and

                entry of the unique category-specific data would preserve the correct tab

                and data.  It is also insanely generous of you to offer to build an example.

                You can¹t imagine how immensely appreciative I am for any light you can shed

                on this.

                 

                I had created 4 layouts one for each asset category and a button bar to swap

                between them, but after input the layout choice is not preserved with the

                record (rather the last layout choice is preserved as you Browse the records

                regardless of the asset category) so that is not a solution.

                 

                I¹ve attached a pdf with sample records of 3 of the categories from the

                separate layout approach, but if the unique characteristics can be handled

                as you suggest, the common fields can be repositioned as necessary and one

                layout incorporating the tabs would be sufficient. That would be so much

                cleaner and infinitely preferable since it would not rely on the viewer to

                elicit the right layout via a button.

                 

                I¹ve outlined the basic elements below hoping  this isn¹t too presumptuous

                of me, but with the overview I think you will see

                instantly if your solution can resolve the display issue.

                 

                 

                 

                There are 4 asset categories (Types of Work):

                 

                Painting/Graphic Arts

                 

                Music Mss & Ephemera

                 

                Books & Printed material

                 

                Sculpture and furniture.

                 

                 

                 

                A. Characteristics common to all assets :

                 

                Asset / ID #

                 

                Author

                 

                Type of Work (the 4 asset categories),

                 

                Creation date

                 

                Title

                 

                Dimensions

                 

                Location (site where works are stored),

                 

                Condition

                 

                 

                 

                B. Unique, category-specific characteristics:

                 

                1. Painting & Graphic Art

                 

                - Media / Technique - text field (Pen and ink on paper, Oil on canvas,

                Aquarelle, etc)

                 

                - Signed - radio button: Yes/No

                 

                 

                 

                2. Music Mss & Ephemera

                 

                - Genre (Musical)- text field (Symphonia, Concerto, Sonata, Quartetto, etc)

                 

                - Number of Pages

                 

                - Signed - radio button: Yes/No

                 

                 

                 

                3. Books and printed material

                 

                - Genre (Literary)- text field (Music History, Reference, etc)

                 

                - Number of Pages - number field

                 

                 

                 

                4. Sculpture & Furniture

                 

                - Material - text field (Bronze, Stone, Oak etc)

                 

                - Signed - radio button: Yes/No

                 

                 

                Infinite thanks for your help.

                Tom

                     

                 

                Tom EckerleSoup to Nuts, Inc.398 Altamont Road

                  Millbrook, NY 12545

                   

                    

                  T: 845 677 5033C: 646 468 9172

                • 5. Re: Question re: assets database
                  siplus

                  The example was already attached...

                  ...it's just that you don't see it in your inbox.

                   

                  here it is:

                   

                  https://community.filemaker.com/servlet/JiveServlet/download/580841-76396/ArtDB.fmp12.zip

                  1 of 1 people found this helpful
                  • 6. Re: Question re: assets database
                    clayhendrix

                    I really like the solution example that you linked to. I'm trying to understand it. I am assuming, but unable to determine for certain:

                     

                    1.) the type dropdown box determines the radio button set by record

                    2.) the onrecordload script accomplishes this,

                     

                    but I cannot understand the onrecordload script and therefore how to accomplish this.

                     

                    I need to understand so I can use it in other types of applications.

                    • 7. Re: Question re: assets database
                      tomeckerle

                      Clay,

                      Siplus who very generously furnished the very elegant solution would be the one to explain it since I am very much a neophyte and not a darabase designer. Yes, the asset type chosen on creation of a new  record triggers the appropriate set of asset characteristics (fields). I haven't had the time to pull apart the scripting done by Silpus to use tabs to structure the solution so more than that I can't say. He would be a much more capable and eloquent guide. Good luck.

                      cheers,

                      Tom

                       

                      Sent from my iPhone, TE

                      • 8. Re: Question re: assets database
                        siplus

                        When you change the category of one record or when you navigate to a different record, the OnRecordLoad script is triggered.

                         

                        This script goes to a specific object by name.

                         

                        There is a tab control on the layout with 4 tabs, each tab has different fields on it (the last one, which is the default tab, has none).

                         

                        On each tab there is a named object, too - the one to which I go via the script.

                         

                        Going to a named object on a tab in a tab control activates that specific tab.

                         

                        a) You don't see the tab control because it has line none and fill none. On all

                         

                        b) You don't see the tab names because they have a padding of 500.

                         

                        a) + b) = tab control is invisible.

                        1 of 1 people found this helpful
                        • 9. Re: Question re: assets database
                          clayhendrix

                          siplus, that is an amazing concept. Specifically, I am referring to activating a tab by activating an object on that tab with a script. Of course, making the tabs invisible is very cool too.

                           

                          Thanks for your example and explanation!