2 Replies Latest reply on Mar 22, 2011 12:03 PM by yuichim

    I need help on solving a bit complicated solution....

    yuichim

      Title

      I need help on solving a bit complicated solution....

      Post

      Hi all,  I am a bit stuck on this part for a while and don't seem to find a light....   I would really appreciate if anyone could brainstorm with me on this...
      What I am working on is a solution for our customer service rep to be able to view a list of our clients and tell who is late on monthly submission.  For example, our product is an annual service and clients can renew each year. Also they submit a name each month and our customer service rep keep track of them.  
      So basically our product is:

      • Annual service  ( every client is differrent on starting. ex: April, July, etc)
      • Clients submit a name each month for 12 month. (starting month depends on when they started the program)
      • Depending on program clients bought, they may have 2 to 10 names to submit each month.

      My challenge is:  How can we make a system to show all related client's list who has not submitted a name yet this month.. and also to show how many month they have not submitted a name.  (ex:  now is March, and company A has submitted a name for December last, so in tern, company A is 3 month behind..)

      I do have couple of table structure that I have created and would like to work it in...

      I have:

      • Orders Table
      • OrderItems Table (keeps track on all the products ordered by a client)
      • Accounts Table

      I hope I am being clear... and I know it is a bit confusing...

      I think this is very close to something like an account system where a telephone company customer service rep can keep track of their custome's payments.    A telephone service is renewable every month, and we have to pay them each month.  Also depending on the options added to the service, we have extra charges  (ex: international calls, call waiting, etc)

      Any help much appreciated.....

        • 1. Re: I need help on solving a bit complicated solution....
          philmodjunk

          Don't you need one more table, a "programs" table where each program that you offer is listed in separate records? (Maybe that's the Accounts table, but I'm reading that as the table where you manage payment info...)

          It's not just a matter of tables, but also in how you use table occurrences to relate them to each other:

          Orders----<OrderItems>-----Programs   ( ---< "means one to many" )

          which, with different names, is the same structure you see in the Invoices starter solution that comes with FileMaker.
          (Invoices---<LineItems>---Products)

          Beyond that, you'll need to add a submissions table where you log the receipt of each submitted name. This record can log the date, submitted name and any other data about that submission that you need.

          LineItems---<Submissions

          If you can set up data entry for Submissions so that if a customer does not submit a name in November, but submits one in December, it is logged as a late submission for November, then you can more easily set up a report by searching a date field in the submissions table but from the OrderItems table. (Find all OrderItems where the date of last submission is less than the first of this month. for one example.)

          • 2. Re: I need help on solving a bit complicated solution....
            yuichim

            Hi Phil!

            Thank you very much as usual! again!

            I do have the table called "winners" in which is like the "submissions" table you have mentioned.   In there, I have the fields:

            • _kf_accountid
            • _kf_orderItemsId
            • _kf_orderItemsTitles  <-  related
            • accountCreate
            • accountMod
            • month
            • timestamp_create
            • timestamp_mod
            • winnername1
            • winnername2

            Here is also the overlook of tables:

            How would filemaker know if a customer submits the name in October but skips in December?  I would like the system to be able to flag that so that our customer service rep can followup on that customer.