6 Replies Latest reply on Sep 26, 2014 1:03 PM by philmodjunk

    Really struggling with this look up field



      Really struggling with this look up field


      I have a Shift Throughput table (STP) that gets input each shift regarding production details.

      One field that is in each STP record is Electrical Usage which is dependent on the equipment used.

      I have a Mill Table which contains a list of the equipment available and the electric used for that equipment.

      I would like to have the electric usage auto fill on each STP record once the equipment is selected for that Invoice_ID ( the primary key field for each production run).  Also I need the electric to stay with the record even if the equipment changes during the run.


        • 1. Re: Really struggling with this look up field

          Additional photo showing Mill Table and relationship

          • 2. Re: Really struggling with this look up field

            There seem to be some details missing here and I can't zoom in enough to tell some of them from your screen shots.

            On what table occurrence is your layout based? Shift Throughput?

            And you have a portal on that layout. On what table occurrence is it based? Mills? Or some other table not shown in the relationships graph?

            It looks like you have one record in STP for each shift worked on a given date. Is that the case?

            If so, it would seem that you'd need a data model similar to this:


            where you'd use a portal to ShiftLog to log what equipment was used (and possibly for how long) during each shift. Each record in ShiftLog could look up (copy) data such as electrical usage from a matching record in Mills. Fields in STP can then compute totals from the records created in ShiftLog.


            • 3. Re: Really struggling with this look up field

              I understand your suggestion, STP is my Shift Log and was hoping to not create another? Or need to enter usage each shift?

              My layout is actually based on Invoice Data and contains a portal from STP.

              So portal 2 above is on a Invoice Data layout and contains fields from STP.

              And portal 1 above currently contains fields from the Mills table on the same Invoice Data Layout.

              One of the fields in STP is Electrical Usage Lookup.


              • 4. Re: Really struggling with this look up field

                What I'm trying to get a clear picture of is how the data in Mills will be used to produce what you need. It seems you have multiple items of equipment with one electrical usage figure each. For any given shift, you have to list all equipment used that shift and access an electrical usage figure for each correct?

                Then the value you want to "look up" is actually a total of all the listed equipment.

                If that's what you have here, the data model I suggested works well for it. I don't see any easy way to do that from your current set up and am not sure how you are currently listing the multiple items of equipment used for a given shift.

                • 5. Re: Really struggling with this look up field

                  Actually only 1.  Any given STP record will have one product, one mill and one usage number.

                  1 InvocieData Record = 1 Product = 1 Mill = 1 Usage number = Multiple shifts

                  • 6. Re: Really struggling with this look up field

                    Then this is very simple. Take a number field in STP, Use Manage | Database | Fields to specify an auto-enter field option for it. You can use an auto-entered calculation or the looked up value setting to copy over the Usage figure from the related Mill Record.