1 2 Previous Next 27 Replies Latest reply on Sep 24, 2013 11:26 AM by MallieWilliams

    Calculation

    MallieWilliams

      Hello Everyone,

       

      Say if I have a field called Status and I have a dropdown list attached to it called Order status. And in that order status list I have Won, Shipped, Canceled , and On Hold. Then I have two Tables Table 1 and Table 2. One one table I have two fields for ex.. Name_First, Name_Last, Table 2 Name_First_s, Name_Last_s..

       

      I want to do an auto enter or script To where if the Status is Won Then the Name_First populate into the Name_First_s.. If that make sense..Which route would be the better or easier to accomplish.

        • 1. Re: Calculation
          Mike_Mitchell

          My first question would be, "Is it really necessary to copy data between the two tables?" Because, in many cases, this is poor database normalization. What happens if Name_First in Table 1 changes after the value in Table 2 is established? Should it be updated in Table 2? Do we allow the data in Table 2 to be updated? And keeping data in two places doubles the amount of space consumed for that piece of data (not a huge concern nowadays, but it does matter).

           

          In most cases, the "echoing" of data from one table to another can be accomplished some other way (establishing a relationship, hiding / exposing a layout object using Conditional Formatting, etc.). There are only a couple of exceptions, and they usually involve displaying data in a portal from another table than the one in the primary TO the portal points to.

           

          Now, the second question would be, "Where is this Status field?" In Table 1? Table 2? Or some other table? Because this defines the options. Underlying the question is, most likely, the fact that calculations do not update when related data change. Hence, you would need to cause it to trigger by some other means. Often, this involves a Script Trigger of some sort, or perhaps a batch job run at night, depending on how immediately you need the refresh to happen.

           

          HTH

           

          Mike

          • 2. Re: Calculation
            MallieWilliams

            I take that back all the fields are in 1 table..You have Name_FIrst and Name_FIrst_s in the same table.There are just two layouts.

            • 3. Re: Calculation
              Mike_Mitchell

              So you just want to display the first name only where Statue = "Won"?

               

              Why not just do a Conditional Format on the field on the second layout?

               

              Mike

              • 4. Re: Calculation
                MallieWilliams

                When the drop down list = Won I want the Name_First fields on the first layout to = Name_First_s on the second layout. If that make sense. If the list shows Canceled or any out drop down than the First_Name field on the first layout will not show on the other layout.

                • 5. Re: Calculation
                  Mike_Mitchell

                  Simple question: why do you have two different fields on the different 

                  layouts?

                  • 6. Re: Calculation
                    Mike_Mitchell

                    See if the attached file helps.

                    • 7. Re: Calculation
                      MallieWilliams

                      This conditional format may work. Let me see if I can modify it. The only issue I see is that my table is larger and I need to have 4-5 specific fields to show on the other layout when its a Won dropdown selection..If that make sense.

                      • 8. Re: Calculation
                        Mike_Mitchell

                        Again: What are you trying to accomplish? There are many - MANY - ways to make the contents of a field appear under certain conditions, and most of them don't involve duplicating the data in your table.

                        • 9. Re: Calculation
                          MallieWilliams

                          Okay.. In layout1 the order status is Won.. So the fields Project Engineer, Sales Rep, Company Name, Nees to populate that information on the layout2 if it's a Won Order Status if not than those fields don't need to auto populate onto layout2. This is what I'm trying to accomplish. So I'm not sure if doing conditional would be the correct method.

                          • 10. Re: Calculation
                            Mike_Mitchell

                            Apparently, I'm not making myself clear.

                             

                            Why do you have extra fields in your database? Do you need:

                             

                            1) People to be able to type extra information over the top of these fields?

                            2) People to be able to search on these fields?

                            3) Just for the information to show up?

                             

                            To what purpose do you want this information to show up? What is the user going to do with these extra fields?

                            • 11. Re: Calculation
                              MallieWilliams

                              This is what the users want one layout with RFQ View and the other Project View with some of the same fields to show up so that they have a view on the other layout. These fields are only on 1 table so it's not just extra fields on two different tables. understand what you are saying. I'm just going by how they want it and to make sure that it works. I explained to you what I was trying to do on the message before this one.

                              • 12. Re: Calculation
                                Mike_Mitchell

                                The fact that the fields are on two different layouts doesn't mean you need two different fields. And the fact that you need the information to show up conditionally is well understood. But you haven't answered my question. Again, what are the users going to be doing with the information? Do they need to enter the data? Do they need to perform a search? Or do they just want to see it?

                                 

                                If they need to enter the data, for example, a calculation field won't work. If they need to enter the data, then you can't use a conditionally-formatted merge field. But for just performing a search, you could use a self-joining relationship based on the status.

                                 

                                What the users need to do will determine the best method.

                                 

                                Mike

                                • 13. Re: Calculation
                                  MallieWilliams

                                  They need to enter data. When an Status is a Won it needs to reflect the fields from the RFQ to the Project View. The data is filled out in the RFQ VIew and then if it becomes a Won status the fields that I name should populate to the project view.

                                  • 14. Re: Calculation
                                    Mike_Mitchell

                                    But will they need to (1) do searches on Name, (2) change the value of Name, or (3) just be able to see the Name?

                                    1 2 Previous Next