1 2 Previous Next 16 Replies Latest reply on Sep 10, 2015 5:34 AM by erolst

    mulitple fields with same script

    gilo

      Hi

       

      I'm trying to create a DB where i can manage my cigar stock.

       

      I have 2 tables:

      cigars : with the detail of the different cigar types and brands I have

      humidor: with the different positions, where cigars can be stocked in my humidor. for each position I have a field with the cigar ID and a field for the icon, later on.

       

      My Humidor has 14 shelfs, each shelf can stock up to 25 cigars. So i would have to create 350 fields for the cigar type and 350 fields for the icon field. And then I would have to add a script to all 350 icon fields manualy.

       

      So my question would be, is there no way that this can be done easier?

        • 1. Re: mulitple fields with same script
          erolst

          gilo wrote:

          My Humidor has 14 shelfs, each shelf can stock up to 25 cigars. So i would have to create 350 fields for the cigar type and 350 fields for the icon field. And then I would have to add a script to all 350 icon fields manualy.

           

          That's not a good idea, for a number of reasons. Whenever you start creating several fields for the same attribute – cigarPosition – you'll probably need a join table …

           

          In this case, a join table between Cigar and Humidor, where each record is “a cigar in a different humidor position”; that means you only need four fields: shelf, positionInShelf, and the two foreign keys humidorID and cigarID (plus, of course, the primary key for the join table).

           

          So your data model id:

           

          Humidor --< CigarBrandInHumidor >-- CigarBrand

           

          meaning: a humidor can have many cigar (brand)s, and each cigar brand can appear in many humidors.

           

          Where does that icon you mentioned come from? If it is stored with a cigar brand (as it should be, if it is an icon for a brand), you don't need to create a field in the join table, but can simply reference CigarBrand::icon.

          • 2. Re: mulitple fields with same script
            andzax

            You can create 3 Table

             

            1- Cigars ( "IDcigars"  "ID_TypeCigars" "ID_Humidor Position" .... description fileld , Icon, etc... )

            2- TypeCigars ( "ID_TypeCigars"  description fileld etc... )

            3 - Humidor ("ID_Humidor Position" description fileld etc... )

             

            Bold field is AUTOINCREMENT numeric field Unique ...

            • 3. Re: mulitple fields with same script
              Mike_Mitchell

              Yes, most definitely. You need what's called a "join table".

               

              The relationship between cigar, cigar icon, and shelf position is such that you have a table where each record represents a shelf position. You have a field for cigarID, cigarIconID, and shelfPositionID. When you need to assign a particular cigar to a particular position, you populate those fields with their appropriate values. (If the number of shelf positions is fixed, then the shelfPositionID never changes.)

               

              Now, further extending the model, if you have 14 shelves with 25 positions each, then you also have a shelf table, and you have a shelfID field in that table, uniquely identifying the shelf. You then link the shelfPosition table to the shelf table by putting the shelfID value as a foreign key into each shelfPosition record. So you will have 14 x 25, or 350 records in shelfPosition (rather than 350 duplicate sets of fields).

               

              If at any time, you add a new shelf, it's a simple matter to create a new record in shelf and 14 new records in shelfPosition.

               

              I suggest you pick up a copy of the FileMaker Training Series and go through the chapter on Data Modeling. It will help you understand how relational database modeling principles are to be applied to a specific problem.

               

              HTH

               

              Mike

              • 4. Re: mulitple fields with same script
                gilo

                Thank you guys for your quick replys.

                 

                Your ideas are helpful, but I also want a graphical display of my humidor, so I'm able to see where and what cigar I have.

                 

                I attach a screenshot of how it could/should look

                 

                I don't know how to get the pictures on one page if for every position I have a different record.Bildschirmfoto 2015-09-08 um 11.19.17.png

                • 5. Re: mulitple fields with same script
                  erolst

                  Mike_Mitchell wrote:

                  Now, further extending the model, if you have 14 shelves with 25 positions each, then you also have a shelf table, and you have a shelfID field in that table, uniquely identifying the shelf.

                   

                  I'd say the requirement for a Shelf table depends on whether a shelf is something you have to say more about than its position in the humidor.

                  • 6. Re: mulitple fields with same script
                    Mike_Mitchell

                    You can use portals for this. It's a bit of a pain, but you can use an individual, single-row portal that starts on the specific row for each position. (Upper left starts on record 1, next one starts on record 2, and so forth.)

                    • 7. Re: mulitple fields with same script
                      Mike_Mitchell

                      Since I don't know what a "humidor" is, I'll take your word for it.  

                      • 8. Re: mulitple fields with same script
                        gilo

                        ok so I have to create 350 portal, ok better than 700 fields ;-)

                         

                        but that's the only way?

                        • 9. Re: mulitple fields with same script
                          erolst

                          gilo wrote:

                          ok so I have to create 350 portal, ok better than 700 fields ;-)

                           

                          What makes you think that?

                           

                          For display, you'd probably display as many portals as there are shelves, each being filtered on shelf# and sorted on positionInShelf.

                           

                          For data entry, you only need a single portal; and if there are 350 positions (i.e. related records) to be created, that can be automated by a script, specifying some parameters (shelf count, # of positions in a shelf etc.).

                          • 10. Re: mulitple fields with same script
                            erolst

                            Mike_Mitchell wrote:

                             

                            Since I don't know what a "humidor" is

                             

                            A refrigerator for cigars – and I assume one shelf in that thing is like any other; but maybe the OP has to say more about that ….

                            • 11. Re: mulitple fields with same script
                              gilo

                              ok but than i don't get a display like on my screenshot, because as far as i know, portals are always rows, I cannot put 2 records side by side in a portal?!?

                               

                               

                              Or am I wrong?

                              • 12. Re: mulitple fields with same script
                                gilo

                                A humidor is a cabinet, where u can store cigars. Cigars need a certain humidity for a better taste.

                                 

                                Shelfs cannot be added. IMG_0017.jpg

                                I want to be able to see in my FM solution what cigar is on the right lower shelf on the right...

                                • 13. Re: mulitple fields with same script
                                  erolst

                                  I hadn't seen your screenshot before posting my previous reply …

                                   

                                  Well, for such a graphical display (especially if you want it staggered) you'd really need a number of one-row portals (as Mike suggested) – or use a series of calculated repeating fields that populate themselves from the join table … (and for which you can grab the related data for the popover by using the same calculation in reverse).

                                   

                                  You may object to the repeating field suggestion with “didn't you just advise against fields?", to which I'd respond “Yes, I did, pertaining to the data model – but this is about display …”

                                  • 14. Re: mulitple fields with same script
                                    beverly

                                    Your graphical display. as I see it, is just that, graphical. You can place buttons on the layout. These would all pass the primary key of the join file (with cigar foreign key & self foreign key - or how ever you have it). You can pass these as script parameters (one script that reads the parameter), you can conditionally format the buttons (if empty, grey it out, for example), you can call a pop-up, you can arrange them however you need to on the layout. It would be easier to create the first and make it work, then duplicate, just changing what gets passed as needed. If you "name" the buttons sequentially (or any manor that makes sense), that might help. You are not creating fields or filtered portals, you are creating buttons with appropriate icon (FM14).

                                     

                                    beverly

                                    1 2 Previous Next