1 2 Previous Next 20 Replies Latest reply on Nov 6, 2016 12:29 PM by beverly

    Paying taxes... and making records of it

    LeszekŁuczkanin

      TABLE: CUSTOMERS

      Customer ID (pk)

      Name

       

         |

         |

         |

         |

        /|\

       

      TABLE: OPERATIONS

      Operation ID (pk)

      Customer ID (fk)

      Date

      Amount

      Period (month)

      Period (year)

       

       

      I have 3 types of customers:

       

      1. Has to pay tax  fixed - every month the same amount.
      2. Has to pay tax unfixed - every month different value
      3. Doesn’t have to pay

      At the end of the year I give back 20% of taxes that was paid to me, so I need to summarize this incomes for everyone and then count 20% of it. And check if I pay it out.

      I need a tool which will show me summary of all customers that has to pay with an amount every one paid each month. I also would like to have a portal (or anything) in my CUSTOMER layout to see how this particular customer was paying this year.

       

      Problem is, that some of customers pay once a month all amount, while another pays few times a month, so sometimes it is 1x 4000,00 $ and another time it is 4 x 1000,00 $ a month.

       

      Any ideas, please?

        • 1. Re: Paying taxes... and making records of it
          beverly

          You've defined very clearly! Summary being the key here. The sum (per year per client) is needed. A Summary Field can give you the value, but you also need to apply that discount/refund. There is a function called

          GetSummary()

          that might help, but must still be unique per client per year.

          Or ...

          The function

          Sum()

          of related records might help, but you would still need to limit per year.

          Or ...

          You might find the records in a given year and loop through them getting the sum and as a result set the value in another table related to the client

           

          clientid, year, sumPerYear, discountToApply, etc.

          And/Or ...

          ExecuteSQL() can be used at any time to narrow down by client by year. Perhaps settings fields as above, so you have historical data.

           

          Sent from miPhone

          • 3. Re: Paying taxes... and making records of it
            beverly

            I was going to try to help you with the ExecuteSQL, but wanted to verify the field names.

            Do you really have a field "Period (year)" instead of "Period_year"? You may find, at some point (using ExecuteSQL for example), that the naming of fields (and other objects) in FileMaker is very important for usage elsewhere.

            https://www.filemaker.com/help/15/fmp/en/index.html#page/FMP_Help/naming-fields.html

            If the name I see in your post is not correct, please post the real name(s)?

            beverly

             

            p.s. I always have a field "yr_mon" which is an auto-enter text field that is alpha-sortable, searchable and handy for grouping (summarizing) for reports and charts:

            Year ( myDate ) & Right ( "00" & Month ( myDate ) ; 2 )

            I might add a "_" between the year and month to make it clear this is text and not a number, but the result is the same for usability.

            • 4. Re: Paying taxes... and making records of it
              LeszekŁuczkanin

              I'm from Poland, so my field names are totally different   I always use examples, and it was only part of my tables.

               

              I'm a beginner with this Filemaker stuff, so I will be thankful for any help "step by step"

              • 5. Re: Paying taxes... and making records of it
                beverly

                this is mostly an outline and you would calculate the final discount amount:

                 

                TABLE: client_taxes

                ===================

                customerID

                year

                sumPerYear

                discountToApply

                 

                IN table customers, I might have a script to query (a loop) each customer

                go to layout (customers)

                show all records

                go to record ( first )

                set variable ( $year ; Year(Get(CurrentDate)) )

                loop

                set variable ( $customerID ; CUSTOMERS::customerID )

                 

                go to layout (client_taxes)

                new record

                set field ( client_taxes::customerID ; $customerID )

                set field ( client_taxes::year ; $year )

                set field ( sumPerYear ;

                  Let (

                  [ ~query = " SELECT SUM(amount) FROM OPERATIONS WHERE period_year = ?

                  AND CustomerID = ?"

                  ; ~result = ExecuteSQL( ~query ; "" ; ""

                  ; $year

                  ; $customerID )

                  ]; if ( ~result = "?" ; "" ; ~result )

                  )

                )

                go to layout (customers) // original

                exit loop if ( get (foundcount) = get (recordnumber) // stop procees after last

                got to records ( next )

                end loop

                 

                I don't know your full set of table names and fields, so this is not going to work "as is".

                beverly

                • 6. Re: Paying taxes... and making records of it
                  LeszekŁuczkanin

                  Wow! I'm on my trip and don't have much time to work now, but I would like to learn the way that you described.

                  I have many questions...

                   

                  FIrst: what is

                   

                  TABLE: client_taxes

                  ===================

                  customerID

                  year

                  sumPerYear

                  discountToApply

                   

                  Do I have to make a new table occurrence or just add new fields to my existing one?

                   

                  What kind of fields are those, how to set them up:

                  sumPerYear

                  discountToApply

                   

                  PS. In my TABLE:customers I have many text fields such as address, telephone number, etc. I have a text field to describe a type of tax (25%, fixed, none). I have also a number field which describes FIXED tax amount (if tax type is different than "fixed" field is empty)

                  • 7. Re: Paying taxes... and making records of it
                    beverly

                    client_taxes is a new table for holding the taxes, since every year they can be different. the customerID is a foreign key, of course. If you store them like this, you have historical records.

                     

                    The sumPerYear was the ExecuteSQL() that is run for each client at the end of each year. Set the field as I described (loosely) in my other reply.

                     

                    The discountToApply is whatever you need based on the value of sumPerYear.  Again, I don't know your fields, so used an example (which can be changed). Or how you will actually use that sum for that year.

                     

                    beverly

                    • 8. Re: Paying taxes... and making records of it
                      LeszekŁuczkanin

                      So there is another question:

                      I have a lot of customers in my base (500). Only 50 of them have to pay taxes and have a unique category. Should I have a separate table for that group or is it enough to filter them with new table occurrence? And how to do that filter by category (with tax)?

                      • 9. Re: Paying taxes... and making records of it
                        beverly

                        by customerID is enough. if you need to add fields to the new table, then do so. If you need to filter by category, then do that.

                        • 10. Re: Paying taxes... and making records of it
                          LeszekŁuczkanin

                          How can I filter by customerID (auto-enter, serial number) ? It is different for every customer...

                          In different places of my database it could be useful to have filtered customers with specified category, but I don't know how to do that.

                          • 11. Re: Paying taxes... and making records of it
                            beverly

                            In the new table it is NOT auto-enter. It is a foreign key relating to the parent Primary Key (which is auto-enter). Re-read my full post.

                             

                            Sent from miPhone

                            • 12. Re: Paying taxes... and making records of it
                              LeszekŁuczkanin

                              I try to explain my last question.

                               

                              My table CUSTOMERS looks like this:

                               

                              ID     NAME         CATEGORY    TAX TYPE     MONTHLY TAX VALUE

                              1     Leszek          Merchant        None               (empty field)

                              2     Darłowo        Shop               25%               (empty field)

                              3     Elbląg           Shop               Fixed               1000,00

                              4     Gdynia          Shop               Fixed               1300,00

                              5     FCK              Enterprise       None               (empty field)

                              6     Poznań         Shop               25%               (empty field)

                               

                              In variuos places of my database it would be very useful to have only CUSTOMERS with CATEGORY "Shop". I think that I should set up a new table occurrency of my TABLE: CUSTOMERS, but I have no idea how to do it to have only "Shop" category in that occurrency.

                              I often use a value list of CUSTOMERS and I need only "Shop" category. Instead I have all of them and that makes searching much more difficult.

                              • 13. Re: Paying taxes... and making records of it
                                beverly

                                ok, so each CUSTOMER has either none (no tax to calculate) or percentage or fixed amount (per month)

                                 

                                This can still be used in a calculation for the final field I showed 'discountToApply'. So perhaps the name is in accurate, as it may be fixed-per-month and have nothing to do with the 'sumPerYear'. And that is ok. I leave it to you to create a Case() statement to get the value.

                                 

                                But you are now asking about "filter-by-category" and I see by your example, that you have the CATEGORY field/column. My question back would be where do you need to show this?

                                 

                                1) you can do a find in a List view that only show those for a particular category (by scripted find)

                                 

                                2) you can have a portal that relates to the customers with a "filter" (part of the portal dialog) that shows just that category

                                 

                                3) if you need a value-list, then you may need a relationship that sets up the list to show only that category.

                                 

                                Again, it all depends upon what you may wish to do with the data of that filtered/found set of records.

                                beverly

                                • 14. Re: Paying taxes... and making records of it
                                  LeszekŁuczkanin

                                  Every customer from group "with tax" has to give me an account book every month. I have a table where I sign every time I get a book from somebody. Table is simple:

                                   

                                  Serial ID

                                  Customer ID (fk)

                                  Year

                                  January

                                  February

                                  ...

                                  December

                                   

                                  When I add new record I describe a Year and pick a Customer from a list and in this moment I have all of my customers and I would like to choose only from those with category "with tax".

                                  1 2 Previous Next