1 2 3 Previous Next 42 Replies Latest reply on Nov 28, 2012 9:05 AM by philmodjunk

    Sum(distinct) equivalent question

    IvanKunijevic

      Title

      Sum(distinct) equivalent question

      Post

      when I am creating relationships in a new table called shipping what should the  fileds be named in  shiping tabel so I can make this relationship... shiping dates ID in the shiping table and also which ones to relate do Line items table and invoice one. ?

      I am trying to folow your advice. below

           Invoices------<ShippingDates------InvoicesByShippingDate----<LineItemsByShippingDate

           InvoicesByShippingDate and LineItemsBYShippingDate would be new occurrences of the invoices and line items tables.

           Invoices::anyField X ShippingDates::any field
           ShippingDates::DateShipped = InvoicesByShippingDate::Ship Date
           InvoicesByShippingDate::InvoiceID = LineItemsBySHippingDate::InvoiceID

        • 1. Re: Sum(distinct) equivalent question
          philmodjunk

               Can you describe what you have and what you want in more detail? What exactly makes the entry "Distinct"?

               Are you using FileMaker 12?

          • 2. Re: Sum(distinct) equivalent question
            IvanKunijevic

                 by distinct I mean a single return value

                 and not one value in the row on a portla in several fields,just first..the next value would be the next total from other realtion conected to this field

            • 3. Re: Sum(distinct) equivalent question
              IvanKunijevic

                   I am using file maker 11

              • 4. Re: Sum(distinct) equivalent question
                philmodjunk

                     I still don't know what you want. A single value from what set of data? All the line item records for the invoice? A selected subset that have a specific value in common?

                     Do you have this typical set of relationships for your invoice?

                     Invoices----<LineItems>-----Products

                     (LineItems are called "InvoiceData" in the FileMaker 12 Invoices Starter Solution.)

                • 5. Re: Sum(distinct) equivalent question
                  IvanKunijevic

                       yes there is a relationship like te one you discribed above

                        

                  • 6. Re: Sum(distinct) equivalent question
                    IvanKunijevic

                         what I want is a total of my products leaving the warehouse, I get them  substracting them from  line items in warehouse...if there are 4 products  then I get total in  several times like this

                          

                         items in           items out                                              total

                         100                    4                                                           20

                         200                     6                                                          20

                         300                    10                                                         20

                          

                          

                          

                    • 7. Re: Sum(distinct) equivalent question
                      IvanKunijevic

                           what I want is this                                         date field 

                           100                                     4                            23/11/2012                                                    20

                           200                                      6

                           400                                       10

                            

                           so next calculation  

                           would be                                                                         date field 

                           100                                             10                                 23 /11/2012                          20

                           200                                             10                                   24/11/2012                          30

                           400                                               10

                            

                            

                           is that posibble

                      • 8. Re: Sum(distinct) equivalent question
                        philmodjunk

                             Specific to one invoice?

                             Your total does not appear to be "distiinct" which makes me think that I am missing something. the total of 20 appears to be a total of all items out and does not appear to have anything to do with items in.

                             I'm thinking you need a summary report layout based on your line items table but am still unclear as to what makes one row of data different from another in your example.

                        • 9. Re: Sum(distinct) equivalent question
                          IvanKunijevic

                               not missing something, I am missing a point, and yes you re right they dont have anything to do with litems in , just items left in the stock .the rows of data would be linked to date when that action is happening or happened , but i dont know how to  link the two

                                

                               if such and such date is set show result of a total of all items leaving the warehouse

                               if new date  then total 2 etc..

                                

                               thats where I blank ...I donnt know how to set values and variables for the fields, link betwweeen dates and totals.

                          • 10. Re: Sum(distinct) equivalent question
                            philmodjunk

                                 Ok, let's see if this is what you have in mind.

                                 You want a total of all items shipped for each date that the item was ordered? or Shipped? (Could be the same, but not in all such businesses).

                                 And you want these totals for a single date? a Range of dates?

                                 This type of report is possible:

                                 Date                   Shipped
                                 11/27/2012               200           
                                 11/28/2012                 30
                                 11/29/2012                 80      

                                 Total                        311

                                 Such a report can, if desired be further broken down by product ID if such is desirable to give you totals for each item shipped by date.

                                 You can create a layout based on your line items table. Replace the body layout part with a sub summary layout part "when sorted by Date".

                                 Define a summary field to compute the total of your Quantity field.

                                 Place the date and this summary field inside the sub summary layout part.

                                 Find the records you want for your report, sort them by date and you'll get the result shown in this post. 

                            • 11. Re: Sum(distinct) equivalent question
                              IvanKunijevic

                                   can you open my file, can you show me I am really strugling to show it on original report, i can show a sumarry like thea on new layout ,but not on the one I need

                              • 12. Re: Sum(distinct) equivalent question
                                philmodjunk

                                     You may not be able to do what I described on your existing report.

                                     I'd have to see your report layout.

                                     You can upload your file to a file sharing site such as Drop Box and then post the download link to it here. (Or Private Message it to me if you don't wish to make the link public.)

                                • 13. Re: Sum(distinct) equivalent question
                                  IvanKunijevic

                                       Here is the file called storage in the Micael folder

                                       the user name is Admin

                                       the password is 123

                                  • 14. Re: Sum(distinct) equivalent question
                                    IvanKunijevic

                                         +whooduld you be so kind to tell me your email , if this hotlink doesnt work

                                         than you

                                         IvaN

                                    1 2 3 Previous Next