1 2 Previous Next 20 Replies Latest reply on Nov 7, 2014 4:22 AM by beverly

    How

    bcfkate

      Hello,

       

      We appreciate the opportunity to ask questions in this forum. So my question is:

       

      We're in the process of updating a database carried over from FileMaker 8 to 13. The database has a NAMES table, an ADDRESSES table, and a MATERIAL_SALES_N_PRICE_QUOTES table.

       

      The NAMES table has an Individual ID field that serves as a primary key.

       

      The ADDRESSES table has an Individual ID match field, as well as an Address for CopyNPaste field (to create labels), and an Address Purpose field. The AddressPurpose field is a repeating field with up to 4 repitions for: "Billing", "Mailing", "Shipping", and/or "Other". About 2/3 of the records in the database have all three options.

       

      The MATERIAL_SALES_N_PRICE_QUOTES table has fields for Shipping Label, Billing Label, and the Individual ID match field.

       

      Currently, whenever someone creates a new order/price quote, they review the addresses available in an addresses portal on the orders layout and select/copy the shipping address into the Shipping Label field, and the billing address into the BillingLabel field.

       

      We would like to automate this process. What is the best way to proceed?

       

      Thanks for your help with this.

        • 1. Re: How
          mikebeargie

          I would start by changing the address purpose field to a checkbox field with the four options, then you'll be able to use patterncount() on the field to find "shipping" addresses. A repeating field I think is kind of irregular in this usage.

           

          For example, you could create a new field, and perform a replace field contents on the new field with:

           

          List (

             GetRepetition( ADDRESSES::Address Purpose ; 1 );

             GetRepetition( ADDRESSES::Address Purpose ; 2 );

             GetRepetition( ADDRESSES::Address Purpose ; 3 );

             GetRepetition( ADDRESSES::Address Purpose ; 4 )

          )

           

          Then use that new field masked with a checkbox set of your four options.

           

          Then a calculation of PatternCount( ADDRESSES::New Purpose Field ; "Shipping" ) > 0, would return true for any address that's a shipping address.

           

          You could even do a more complex ExecuteSQL() calculation to select the shipping address directly when you need it.

           

          Technically, you *could* calculate the shipping and billing addresses from the order quote directly, but it would be a pretty ugly.

          1 of 1 people found this helpful
          • 2. Re: How
            bcfkate

            Thank you for taking the time to respond to my question.  You’re quite right about making the Address Purpose field a non-repeating checkbox field.  Thanks for catching that. 

             

            I can create a portal from the MATERIAL_SALES_N_PRICE_QUOTES table to the ADDRESSES table and use a filter (MatlSales_ADDRESSES::Address Purpose="Shipping") to display just the shipping addresses (or MatlSales_ADDRESSES::Address Purpose="Billing" for just the billing addresses). 

             

            The person entering the data then could manually copy the appropriate Shipping/Billing address from the appropriate MatlSales_ADDRESSES::Address portal and paste it into the appropriate MATERIAL_SALES_N_PRICE_QUOTES shipping or billing label field.

             

            I’d like to set it up so that the database automatically supplies the content for the MATERIAL_SALES_N_PRICE_QUOTES shipping and billing label fields but

            1. Allows the user to edit the address if necessary (for instance, if the customer wants the materials shipped to a friend on a one-time basis),
            2. Retains the MATERIAL_SALES_N_PRICE_QUOTES shipping and billing label fields for historical purposes.

             

            I was thinking of defining the MATERIAL_SALES_N_PRICE_QUOTES shipping and billing label fields to Auto-Enter Looked-up value, but don’t see how to access the calculation engine to select only the appropriate address for the field. 

             

            I also thought about using the Auto-Enter Calculated Value and specifying:

            If ( PatternCount ( MatlSales_ADDRESSES::Address Purpose ; "Shipping" )>0 ; MatlSales_ADDRESSES::Address for CopyNPaste )

             

            But that left the Shipping Label field empty.

             

            What am I missing, or is there a better way?

             

              Thanks. 


            • 3. Re: How
              BruceRobertson

              If you expect to get meaningful help, it is generally much better to give a meaningful title to your post.

               

              "How" does not qualify.

              • 4. Re: How
                erolst

                BruceRobertson wrote:

                If you expect to get meaningful help, it is generally much better to give a meaningful title to your post.

                "How" does not qualify.

                 

                While you're on a rant, why not expend some energy towards the dimwits who can't be bothered to use their spell checker, and so make reading their posts a torture (which is why I don't bother) …

                 

                bcfkate at least asks questions in a structured way and says “thank you” – now she/he can learn about forum etiquette from the sage masters …

                • 5. Re: How
                  erolst

                  bcfkate wrote:

                  I was thinking of defining the MATERIAL_SALES_N_PRICE_QUOTES shipping and billing label fields to Auto-Enter Looked-up value, but don’t see how to access the calculation engine to select only the appropriate address for the field. 

                   

                  I also thought about using the Auto-Enter Calculated Value and specifying:

                  If ( PatternCount ( MatlSales_ADDRESSES::Address Purpose ; "Shipping" )>0 ; MatlSales_ADDRESSES::Address for CopyNPaste )

                  […]

                  What am I missing, or is there a better way?

                   

                  Some hints:

                   

                  1. Don't use repeating fields for your data

                   

                  2. Don't use copy and paste.

                   

                  3. For everyone's sake, stop using ALLCAPS in your identifiers; you may not have heard the latest research findings: ALLCAPS are the #1 cause for eye cancer …

                   

                  That being said:

                   

                  If you want to automate the address entry to the correct type, you could create a relationship to addresses by customerID, then use an auto-enter calc along the lines of

                   

                  Case (

                  ValueCount ( FilterValues ( List ( Addresses_forCustomer::addressType) ; "billing" ) ) = 1 ;

                  ExecuteSQL ( " SELECT cAddressBlock FROM Addresses WHERE id_customer = ? and type = ? " ; "" ; "" ; id_customer /* the one from your invoice-like table */ ; "billing" )

                  )

                   

                  which, as you can see, will input the calculated address if there is only one (1) of its kind. Analogous for "shipping".

                   

                  Actually, you could perform the condition query also by ExecuteSQL(), but you need that other relationship anyway, and I wanted to make a point for …

                   

                  Hint #4: Don't use PatternCount() when FilterValues() does a better job.

                  1 of 1 people found this helpful
                  • 6. Re: How
                    mikebeargie

                    PatternCount() is easier for beginner developers to understand than FilterValues() is. And I might even go as far to politely disagree with you and say that this is easier than yours:

                    Case (

                    PatternCount ( List ( Addresses_forCustomer::addressType) ; "billing" ) = 1 ;

                    ExecuteSQL ( " SELECT cAddressBlock FROM Addresses WHERE id_customer = ? and type = ? " ; "" ; "" ; id_customer ; "billing" )

                    )

                    In essence, your use of ValueCount() in this case is basically equivalent to PatternCount(), just more complicated because you need to nest two more functions in order to achieve the same result. With there only being 4 static values of "addressType", I fail to see why FilterValues() does a better job in this case.

                     

                    Part of the beauty of FileMaker is it's forgiveness for novice developers to be able to use easier to understand functions until they adapt to more sleek techniques and function usage later on.

                     

                    That said, there are a few more notes to add to what you already noted:

                     

                    1) To clarify, the above case statement for bcfkate's purpose is required to be an auto-enter text field in the quotes table. That way when a new quote is created it pulls the billing address through to the quote, and also allows for a manual change later if the user wants to.

                     

                    2) There is no accounting for more than one of the same type of related address with the above calculation. If there are two separate related billing addresses, the calculation returns blank.

                     

                    3) Your ExecuteSQL() statement will also fail to return a billing address that has other values checked, or leading or trailing line breaks which are often present in FM checkbox text fields. Replacing type = ? with something like type LIKE %?% will account for that. I'm not quite sure that the %?% is correct as I try and avoid it in my own calcs.

                     

                    Hopefully this feedback is helping and not confusing to bcfkate.

                    • 7. Re: How
                      erolst

                      Mike Beargie wrote:

                       

                      PatternCount() is easier for beginner developers to understand than FilterValues() is. And I might even go as far to politely disagree with you and say that this is easier than yours:

                      In essence, your use of ValueCount() in this case is basically equivalent to PatternCount(), just more complicated because you need to nest two more functions in order to achieve the same result. With there only being 4 static values of "addressType", I fail to see why FilterValues() does a better job in this case.

                       

                      Part of the beauty of FileMaker is it's forgiveness for novice developers to be able to use easier to understand functions until they adapt to more sleek techniques and function usage later on.

                       

                      I see your point in an abstract way, but …

                       

                      • when will “later on“ be, exactly, if someone doesn't show/tell them?

                      • who will remember to adapt that calculation when some day that value list is amended in a way that will break PatternCount()?

                      Mike Beargie wrote:

                       

                      3) Your ExecuteSQL() statement will also fail to return a billing address that has other values checked, or leading or trailing line breaks which are often present in FM checkbox text fields.

                       

                      That's correct; so this would work:

                       

                      Let (

                        typeList = List ( Addresses_forCustomer::addressType) ;

                        Case (

                        PatternCount ( typeList ; "billing" ) = 1 ; // if you insist …

                        ExecuteSQL ( "

                          SELECT cAddressBlock

                          FROM Addresses

                          WHERE

                            id_customer = ? AND

                            ? IN ('" & Substitute ( typeList ; ¶ ; "','" ) & "')

                          " ; "" ; "" ; id_customer ; "billing" )

                      ) )

                       

                      where placing the single quotes correctly is the key to success …

                      • 8. Re: How
                        keywords

                        Touche! Which particular "dimwit" posted this: "I see your poj tin an abstract way"?

                        • 9. Re: How
                          erolst

                          Why don't you go and touché yourself – if you pardon my French!

                           

                          Is there anything substantial that you want to contribute?

                          • 10. Re: How
                            mikebeargie

                            Gentleman, save the white glove slapping for somewhere else.

                             

                            Erolst, Bruce is right, when you’re reviewing topics in the list, “How” is not a subject line that provides any detail.

                            Bruce, new people to the forum don’t know the best way to ask questions, let’s just focus on helping.

                            • 11. Re: How
                              keywords

                              Agree with all, Mike. No offence was meant by me, just a weak attempt at humour.

                              • 12. Re: How
                                bcfkate

                                Thanks Mike and Erolst for you for your thoughtful replies.  Since I'm new at this kind of thing and have only worked in the FileMaker context in the past, I'm not really familiar with SQL, so the ExecuteSQL command is a bit beyond my current skill set.  Is there a way to get to the correct record without using the Execute SQL command?

                                 

                                (As an aside, when I prepared the original question, I had a title that was something like "How to select the correct related record in an auto enter look up" but when I hit the upload button, somehow I accidentally deleted most of the subject line text.  Is there a way to fix that kind of problem on this site?)

                                • 13. Re: How
                                  erolst

                                  keywords wrote:

                                  Agree with all, Mike. No offence was meant by me, just a weak attempt at humour.

                                   

                                  Same here. Let's kiss (or not) and make up.

                                  • 14. Re: How
                                    StanMillar

                                    bcfkate

                                     

                                    (BTW your name would be helpful so I could address you respectfully.)

                                     

                                    I have read all the posts on this topic and suggest the following:

                                     

                                    Why not pull the address data out into its own Table linked by NAMES ID to the NAMES Table with a portal in the NAMES layout? This allows you to have an unlimited number of addresses attached to each record in the NAMES Table.

                                     

                                    That way, you can simply have a field in the ADDRESS Table that signifies which record is to be used for mailing, formatted as a check box in the portal. No copy/paste used. No duplication of data except for the ID field.

                                     

                                    What I do when mailing is to use a series of SetField steps to pull the data from the ADDRESS Table into a separate table with no records until one is created at the time of mailing. You can then let the user access a layout in this LETTER Table to change the address at times that this is required. If you want to get fancy, you can even set up a routine to allow the address flagged for mailng to be changed through the portal and can de-select, if required, the currently flagged mail record simply by making the check box a button and attaching a script.

                                     

                                    If the record in the MATERIAL_SALES_N_PRICE_QUOTES table needs to be retained (as is done, eg, with an Invoice), keep the recods in the table and create new records as needed but using the same SetField process. Using SetField allows the users to change the data as required - no calculation fields involved.

                                    1 2 Previous Next