7 Replies Latest reply on Jan 23, 2013 9:50 AM by Terri

    Auto Field Generation - Creative Ideas Needed

    Terri

      Title

      Auto Field Generation - Creative Ideas Needed

      Post

           Working with FMPro 11 Advanced
            
           Database Name: Data Warehouse
            
      Tables: 
           Workhorse Data
           Workhorse Styles
      Relationship: Style Number & Season & Year
            
           The Workhorse Data table contains a record for each style number and color. So one style may have 5 colors resulting in 5 records for that style. The Workhorse Styles table imports each unique style and then uses a portal to show all the colors related to that style for that season. 
            
           The purpose of the database is to analyze the color choices for a style and determine which color of a style should be photographed and which colors can be simply "cloned" from that first photo. So in my example of 5 colors for a style it may break out to this:
            
           Color 1 - Red - First choice to shoot
           Color 2 - Navy - Not first choice but ok to shoot
           Color 3 - Green - Not first choice but ok to shoot
           Color 4 - Purple - Not first choice but ok to shoot
           Color 5 - Plaid - Must shoot on its own
            
           In this example there would be two shot notes created.
            
      Shot Note One
           Preferable Color
           Red
           Also Available
           Navy
           Green
           Purple
            
      Shot Note Two
           Preferable Color
           Plaid
            
           I've been challenged to eliminate as much typing as possible - to save time as well as improve accuracy. So I have a field called Shot Note (calculation). What I have invisioned in my head is some part of the shot note will automatically populate from other fields. But as for colors, the user will click on a color on the portal and it will populate the designated area Preferable Color or Also Available. The end result is what you see below. It needs to be in one field (so it can be imported into another program) and the spacing/hard returns need to be the same.
            
           Preferable Color
           Red
            
           Also Available
           Navy
           Green
           Purple
            
           Style with a belt<----- This is another field that will automatically populate
            
           Style View - Front<-----This is another field that will automatically populate
            
           Notes<----- This is another field that will automatically populate
            
           So, the final question is, what is the easiest way to get those colors to populate based on the user selection. My first thought is a script trigger when selected but how do I differentiate between the two categories? That is only one of my questions but let's start there for now. 
            
           Your creative suggestions are appreciated.
            

        • 1. Re: Auto Field Generation - Creative Ideas Needed
          philmodjunk

               The Workhorse Styles table imports each unique style and then uses a portal to show all the colors related to that style for that season.

               That implies a third table used for the portal. How is it related to Workhorse Styles? Or is this a portal to Workhorse Data?

               In any case, you can set up a text field with a set of checkboxes for each of the also available colors. When you select several checkbox values for the same field, the values are entered as text separated by returns so this will already give you the return separated list you show in your initial post.

               The key trick will be to get that field's check boxes to list only the colors for that one Workhorse Styles record. If the number of colors are fairly consistent from record to record, a conditional value list may be used. If not you can configure buttons in your portal so that it looks and functions like a set of checkboxes, but happens to have a scroll bar (thus allowing greater variation in the number of colors listed).

               Which looks like the better approach? the conditional format (assumes number of colors only varies by one or two colors from record to record) or the portal based set of check boxes (more flexible, but takes some extra scripting)?

               Either approach allows for very rapid selection of colors as you only need click a button or check box next to each listed color to select it or de-select it.

          • 2. Re: Auto Field Generation - Creative Ideas Needed
            Terri

                 It is a portal to Workhorse Data.

                 Option two works best because the number of colors for each style can vary significantly. It would also need to be filtered by Season.
                  
                 I'm so relieved that you are making this sound relatively simple. I've seen other databases that have this function but I've never been able to figure out how it works.
                  
                 Thanks for your quick response!
            • 3. Re: Auto Field Generation - Creative Ideas Needed
              philmodjunk

                   Here's an older .fp7 file that demo's the technique: https://www.dropbox.com/s/ti8vep64h67mtj2/CheckboxWScrollBar.fp7

                   It does something a bit unusual in that it uses a calculation field with a check box format and value list to produce the check box in the portal row. This is an "old school" way to do this. It's also possible to use the Letter "X" or a "tickmark" character on your layout with conditional formatting used to make it appear and disappear to show which values are selected.

                   In the demo file, List ( Selected Products::ProductName ) would produce a return separated list of Product Names much like you need in your calculation to combine the data into your export field.

              • 4. Re: Auto Field Generation - Creative Ideas Needed
                Terri
                     This looks great, but there are some things about my set-up that will require some changes.
                      
                     First, one check box isn't going to do it for my needs, in fact I think buttons will work best. Because my portal is going to list all colors the user will need to identify if a color selection is for the "Preferred" list or the "Also Available" list.  So I'm thinking two buttons on each portal line will allow them to make a selection. 
                      
                     I don't understand the "product is not in list" portion of your script. And I'm not sure if that will be necessary because my portal will filter by calculation anyway. So do I only need the Else part of your script?
                • 5. Re: Auto Field Generation - Creative Ideas Needed
                  Terri

                       Phil, just a quick update. I was able to add a button for each category and it will populate the list correctly. However, I didn't consider the part where they may need to remove and item from that list. So that is leading me to a Yes/No check box - but either way, I'm not sure how to get the script to remove an item. Is that your If (IsEmpty) part of your script?

                  • 6. Re: Auto Field Generation - Creative Ideas Needed
                    philmodjunk

                         In my demo file, what looks like a checkbox is really a check box field set up as a button. Clicking the check box does not enter data in that field, it performs a script that changes the value of a field storing a list of ID numbers. The field, which is a calculation field then updates to show an x in the check box if that portal record's id number is present in the list of values.

                         And when you click a check box button that shows the value selected, the script removes that item from the list of values and the check box formatted calculation field automatically clears.

                    • 7. Re: Auto Field Generation - Creative Ideas Needed
                      Terri

                           I follow you now - let me see if I can replicate.