3 Replies Latest reply on Jun 23, 2011 1:59 PM by philmodjunk

    Auto Enter Calculation pulling from 2 fields

    mgxdigital

      Title

      Auto Enter Calculation pulling from 2 fields

      Post

      Hello!

      I have 3 different databases (roll, sheet, invoice) and i'm wanting to put a field in one that pulls from the other 2.

      So, say i have my database for roll items and one for sheet items. In the invoices databse i want to have one field called puchase order and have it be an auto enter for whichever PO (either roll or sheet) that has a value.

      I just dont want to have to have 2 fields in invoices (one for roll PO and one for sheet PO). I just want to have one.

      Is this do-able?

        • 1. Re: Auto Enter Calculation pulling from 2 fields
          philmodjunk

          Your post doesn't give a complete picture of your database design and intended function so apologies if I misunderstand or assume incorrectly.

          Each databse is a separate file or are these different tables in the same file? (much of the time it makes little difference, but you never know when it will turn out to be an important detail to consider.)

          Rolls is a a table for listing purchase orders for Roll stock and Sheets is a table for listsing Purchase orders for sheet stock?

          Why not put Roll and Sheet purchase orders in the same table?

          Are these all the tables in your system or only the ones that seem relevant to your question? (Seems like you're missing some tables for a complete system here.)

          Why do you need a PO on an invoice? (Invoices normally document transactions where you sell goods or services to a customer. Purchase Orders normally document transactions where you purchase goods or services. Linking the two by PO seems unusual.)

          • 2. Re: Auto Enter Calculation pulling from 2 fields
            mgxdigital

            Hey Phil!

            We're a manufacturing facility. So we manufacture roll items and sheet items. They are 2 completely different products in our industry.

            So, we have a roll database and a sheet database. Each of those consist of order tickets, packing slips, etc. We have to have them seperate since they're soo differently manufacturered.

            At the end of whole process, the invoice for each system pretty much looks the same.

            There are fields that are in both systems, like the customer PO number, tracking numbers, things like that. Again, i have to stress that they need to be kept seperate.

            But for invoices, i'm trying to import both systems to one invoicing system. This is why i want one field in invoices.

            • 3. Re: Auto Enter Calculation pulling from 2 fields
              philmodjunk

              Yes, but they don't really need to be kept separate. I worked in a company where they produced wine corks, screw caps and bottle capsules--completely different materials and manufacturing methods with very different production and material purchasing requirments. You could, in fact, work for a company that supplies them with sheet/roll stock for all I know. Wink

              They still had a common table of SKUs for all items purchased and produced. Related detail tables where used to document the "very different" parts of their specifications. Such a common table makes inventory management, PO's and invoicing much simpler to manage as any fields common to all materials can be part of the same table, but separate "detail" tables can be used to document the parts that are "completely different".

              To look up from two different tables into the same field, you can use an auto-enter calculation that references both tables:

              If ( RelatedTable1::ID ; RelatedTable1::PO ; RelatedTable2::PO )