3 Replies Latest reply on Aug 11, 2010 9:26 AM by philmodjunk

    Creating a relationship with value lists?

    mgxdigital

      Title

      Creating a relationship with value lists?

      Post

      I have a table called "job tickets"  This is where all my company's jobs are held.

      I want to make a relating table called "reserve stock" which will only show job tickets that are marked as reserve stock for customers. The tickets are marked "reserved" via a radio button field with a "1" value to use to check the box.

      So my first question is can you create a relationship based on a value set?

      Additionally I have my reserve stocks with levels in a "Customers" table.

      I looking to somehow in my "reserve stock" layout show all job tickets that are marked as reserve stock. Then add up the specific products by customer/product id. Then compare them to the stock level in my "customer" table and conditional format them to red if we need to order in more stock.

      This is way to over my head. I would really appreciate any help walking me through this!

      Thanks so much!

        • 1. Re: Creating a relationship with value lists?
          philmodjunk

          You currently have at least two two tables, Job Tickets and Customers. You want a table for reserve stock, but say you "have my reserve stocks with levels in a "Customers" table" This makes no sense to me as I read it.

          Presumably you have a table for Job Tickets, a table for Customers and a table for "stock". Do you have such tables? Do you have others? How are they related?

          I'm guessing here that you need some kind of table to track inventory levels to compare against re-order points so that you can know when to order more stock. I'm guessing "reserved stock" is stock held on hand, but treated as "sold" (can't be delivered to a different customer) when it comes to determining inventory levels and re-order points.

          That sound right?

          What version of Filemaker are you using?

          • 2. Re: Creating a relationship with value lists?
            mgxdigital

            Yes, everything you explain is exactly what i want.

            I'm using FM11.

            Right now, i have a job ticket table that is related to the Customers table by Customer Name.

            I want a Stock table, but i haven't created it yet. It will probably be a occorace of the Job Ticket table but only showing hte jobs that are marked as "reserve stock"

            • 3. Re: Creating a relationship with value lists?
              philmodjunk

              I don't see how you can make that part of your job ticket table. Surely you deliver/sell more than one kind of item and a given job ticket could list multiple items?

              I'm picturing this set of tables (---< stands for one to many)

              Customers----<JobTickets----<Inventory>----Products

              In products, you make one record for each product. You'd have the name, cost information and re-order points recorded here.

              In jobTickets you make one record for each Job. A customer could have many jobs, a Jobticket can, via portal to Inventory, list many items.

              Inventory serves as a "log" or "ledger" that documents each change in inventory. Each time product is received, delivered/reserved or removed for other reasons (damaged, returned to vendor, shrinkage... etc.) an entry would be made that adds/removes product from inventory. Summary fields in Inventory can compute inventory levels for each product and conditional formats can compare those inventory levels to re-order points to flag those items that need resupply.

              The fact that items listed on a job ticket are "reserved" can be handled by adding fields in Inventory that record this status for each inventory item.