1 2 Previous Next 16 Replies Latest reply on Dec 29, 2010 11:46 AM by philmodjunk

    Unique Records - Formulae approach

    Kaps_1

      Title

      Unique Records - Formulae approach

      Post

      I am trying to identify the unique records within a table and struggling. The following link shows a number of screen shots of my database :-

      http://www.4shared.com/file/AFdY3lb3/forum_post_4.html

      I am trying to identify the unique values of the field "Task Name" in the table "Labour on Quote". I have set up another table called "Customer View of Labour" and used Task Name as the indexing field. I have then set up a portal on a layout. However I am getting blank when I go to browse mode - no tasks are appearing.  Any thoughts ? Thanks

      Kaps

        • 1. Re: Unique Records - Formulae approach
          philmodjunk

          Count ( taskname ) will only return 1 or 0. It will return 1 if the field taskname in the current record of Customer View of Labour is not empty, 0 if it is.

          Use Count ( Labour on Quote::TaskName ) to get a count of all the related records with the same task name as the current record in Customer View of Labour.

          • 2. Re: Unique Records - Formulae approach
            Kaps_1

            Thanks.  I now have :-

            If ( Count ( Labour_On_Quote::Task_Name)>1; 1 ; 0 )

            but the portal still shows no values for task names /  frequencies. Any thoughts ?

            regards

            Kaps

            • 3. Re: Unique Records - Formulae approach
              philmodjunk

              What table occurrence ( box in your relationship graph ) is your portal based on?

              What table occurrence is the layout based on?

              These are specified in the "show records from" drop downs found in portal setup... and layout setup...

              • 4. Re: Unique Records - Formulae approach
                Kaps_1

                The layout is based on the "customer" table. The portal is based on "quotes" table. Thanks

                regards

                Kaps

                • 5. Re: Unique Records - Formulae approach
                  philmodjunk

                  And that's the problem. They are based on the wrong table occurrences for this to work. You defined the calculation to evaluate "from the context of Customer View of Labour". Thus, to use the calculation field as written, your portal should refer to Customer View of Labour.

                  If table occurrence is a new term, you might want to read this thread:  

                  Tutorial: What are Table Occurrences?

                  • 6. Re: Unique Records - Formulae approach
                    Kaps_1

                    thanks. I have changed the portal to be "customer view of labour" and there are still no entries. Any further thoughts ?

                    Also thanks for reference to table occurences.

                    • 7. Re: Unique Records - Formulae approach
                      philmodjunk

                      You have:
                       Customer----<Quotes-----<Labor On Quotes>----<Customer View of Labour

                      WIth the layout based on Customer, you'll only see records in the portal if there are matching records in Quotes, Labor on Quotes and Customer View of labor. That's a potentially problematic as a missing related record in Quotes or Labor On Quotes could break the relationship between the customer record and any matching records in Customer View of Labour.

                      • 8. Re: Unique Records - Formulae approach
                        Kaps_1

                        ok - can you suggest an alternative approach ? thanks

                        • 9. Re: Unique Records - Formulae approach
                          philmodjunk

                          I'm not clear enough on what you are trying to do here.

                          Do you want a count of every time a specific value is entered in the task name field of Labor On Quote? Only those that are related to a given customer? A given quote?

                          Do all the boxes in your relationship graph refer to different data source tables (Tables listed on the tables tab)?

                          • 10. Re: Unique Records - Formulae approach
                            Kaps_1
                            Hi, I am trying to produce a quote for the customer. On the quote, the cost of labour needs to be broken down by the task. Hence the first thing to do would be to identify the distinct tasks, and write them to the layout. I'm not entirely sure I understand your question As far as I understand your question all the boxes in the relationship graph do refer to different source tables. Thanks Regards Kaps
                            • 11. Re: Unique Records - Formulae approach
                              philmodjunk

                              I'm not entirely sure I understand your question

                              You can place more than one "box" in Manage | Database | Relationships that refers to the same table listed in Manage | Database | Tables. These boxes are referred to as Table Occurrences and are a key factor in how any layout, script, portal, or calculation functions. For more on this, see:  Tutorial: What are Table Occurrences?

                              I'm trying to determine the context of your effort to count records with the same Task Name. I'm not sure how you can use this count to be able to break down the quote's labour cost by task. I can think of several ways of doing this in a summary report where you pull up the records and group them by task name, but don't see how the number of such records for a given quote will help you do that.

                              That said, I think this is what you are asking for:
                              Create a new table occurrence of Labour_on_Quote by selecting it and then clicking the button with two plus signs at lower left corner. Name this new occurrence, Labour_Same_Quote_Same_Task. Drag from Labour_On_Quote to this new occurrence to create this relationship:

                              Labour_on_Quote::Quote_id = Labour_Same_Quote_Same_Task::Quote_id AND
                              Labour_on_Quote::Task_Name = Labour_Same_Quote_Same_Task::Task_Name

                              This will match a given Labour_on_Quote record to any other record that has the same Quote ID and Task Name.

                              Then a calculation field defined in Labour_on_Quote as: Count ( Labour_Same_Quote_Same_Task::Quote_id ) will return the total number of such records.

                              • 12. Re: Unique Records - Formulae approach
                                Kaps_1

                                Not quite - I am looking for unique tasks. I have explained more in the following doc :-

                                http://www.4shared.com/file/s2d34QrN/forum_post_5.html

                                thanks again for this.

                                kind regards

                                Kaps

                                • 13. Re: Unique Records - Formulae approach
                                  philmodjunk

                                  And if the task is unique within a given quote, the count will be 1.

                                  • 14. Re: Unique Records - Formulae approach
                                    Kaps_1

                                    I think I have confused you. Quoting from your post above (6:17 p.m GMT) :-

                                    I can think of several ways of doing this  in a summary report where you pull up the records and group them by task  name

                                    is EXACTLY what I want !! Can you explain how to do this ? thanks

                                    regards

                                    Kaps


                                    1 2 Previous Next