12 Replies Latest reply on Jan 22, 2013 7:44 AM by taylorsharpe

    Usage of reference versus filling field via script

    francishunger

      Hi,

       

      I do have the choice to either fill in values into a field via a skript, that fetches data from any field and fills it into the target field, OR I could use a reference and refresh via Skript this reference from when necessary. Has any of these methods advantages or disadvanteds over the other?

       

      Thanks in advance,

       

      Francis

        • 1. Re: Usage of reference versus filling field via script
          taylorsharpe

          The lookup option will refire the lookup if the related fields of the lookup are changed and it will happen on every layout you have that field.  The script will only run when you run or trigger the script.  There are times when each is preferable over the other.  I hope that makes sense. 

          • 2. Re: Usage of reference versus filling field via script
            comment

            francishunger wrote:

             

            I do have the choice to either fill in values into a field via a skript, that fetches data from any field and fills it into the target field, OR I could use a reference and refresh via Skript this reference from when necessary.

             

            Not sure what you mean by "reference". In any case, the ideal in a relational database is to store each piece of data in one place only. When you need to use it in another place, you can fetch it from the related table - either directly or via an unstored calculation field.

             

            If you are doing anything elese, make sure you have a good reason for it - because (among other things) any redundancy exposes you to the risk of having conflicting data.

            • 3. Re: Usage of reference versus filling field via script
              francishunger

              Thanks Michael and Taylor,

               

              the scenario that I'm having in my opinion asks for actually doubling the data. For a clients and products database I want to generate orders and bills. Since prices can change or addresses, I want to make sure I can revisit the details of these orders or bills at any time, even after addresses or prices for a certain product have changed. Does it make sense? Does that change anything in the argument reference versus skript?

               

              Thanks in advance,

               

              P.S. Maybe I should note, that I'm aware and acutally using the relational features of FM ...

              • 4. Re: Usage of reference versus filling field via script
                taylorsharpe

                I would use a Lookup that brings over the price when you create the order.  And I would use conditional formatting so you could tell when a price is different than when the order was placed.  I personally would use a script because the Lookup method has possibility of changing the price if you change the related field (e.g., item number) and a script would not. 

                • 5. Re: Usage of reference versus filling field via script
                  comment

                  francishunger wrote:


                  Since prices can change or addresses, I want to make sure I can revisit the details of these orders or bills at any time, even after addresses or prices for a certain product have changed. Does it make sense?

                   

                  Yes, it does -  and this is exactly what lookup is for:

                  http://www.filemaker.com/12help/html/relational.11.22.html#1033096

                  • 6. Re: Usage of reference versus filling field via script
                    comment

                    taylorsharpe wrote:

                     

                    I the Lookup method has possibility of changing the price if you change the related field (e.g., item number) and a script would not. 

                     

                    More precisely, if you change the local field that serves as the matchfield, the price will be relooked up. And that's a good thing.

                    • 7. Re: Usage of reference versus filling field via script
                      taylorsharpe

                      It being a "good" thing depends on what behavior you want.  If you always want it to show the related value, then I would instead just show the related field and skip any lookups or script step calculations. 

                      • 8. Re: Usage of reference versus filling field via script
                        comment

                        taylorsharpe wrote:


                        If you always want it to show the related value

                         

                        That's not at all what I said.

                        • 9. Re: Usage of reference versus filling field via script
                          taylorsharpe

                          No, but you said the Lookup was a "good" thing and I find such comments unhelpful.  It is simply a behavior that is sometimes desired and sometimes not and you use it accordingly.  I wanted to separate the point you were making about functionality from things being "good".  It is more about understanding how they work.  That is what I was trying to convey. 

                          • 10. Re: Usage of reference versus filling field via script
                            comment

                            taylorsharpe wrote:

                             

                            No, but you said the Lookup was a "good" thing

                             

                            No, actually that's not what I said either - although I see nothing wrong with saying it. What I did say that the automatic relookup that occurs when you modify the matchfield is a good thing - and I stand behind that statement. So far, you have said nothing to the contrary.

                             

                            taylorsharpe wrote:

                             

                            I find such comments unhelpful.

                             

                            That is your privilege, but given that the OP has clarified they wish to preserve historical data, I believe the recommendation to use a lookup is indeed helpful. You seem to make a distinction between the behavior of a"native"  lookup versus using a triggered script to copy the related value - but you haven't explained why there would be any difference - or why such difference, if present, would be beneficial.

                            • 11. Re: Usage of reference versus filling field via script
                              francishunger

                              Thanks a lot to Michael and Taylor for their discussion. I still feel, it is not clear at which use case to use which option. Maybe others can chime in with examples, or maybe there are articles in books etc. (that I'm unaware of) that discuss that matter?

                              • 12. Re: Usage of reference versus filling field via script
                                taylorsharpe

                                Well, that is the big question.  A good exmample where a lookup is good is if you have an Order or Invoice, you want it to pull over the current price at the time of the Invoice, but you do not want it to change after the invoice goes final. That is where a Lookup works well.  If you just want to see what the current price always is, you can just show the price field from the products/items table in the layout.  If you can make the item price in the Order or Invoice a calcuation field equal to the price in the products/items table.

                                 

                                Lookups are simpler and work across various layouts and I think for the situation you describe, I would use a lookup.  But I would also suggest have a conditional format on that field that slightly changes its color to let you know when what is in the Orders' price field is not the same value as what is in the Products/Items table.  You can choose to do a lookup again to update it or leave it be if it is a closed Order/Invoice. 

                                 

                                In my example, I am assume there is a Orders table with a relationship back to a Product or Items table, but these obviously could be any related tables.

                                 

                                The advantage of a Lookup over a script is that it will happen on every layout for that record where you update the related lookup field.  A script must be set to run for each layout.  Think of using the Lookup as controling behavior at the schema level and scripting as controling behavior at the user interface leve.  Not sure if that helps, but good for a discussion point.