1 2 Previous Next 26 Replies Latest reply on Sep 23, 2013 11:57 AM by philmodjunk

    Showing last record while entering new record

    LarsChristensen

      Title

      Showing last record while entering new record

      Post

           Hi,


           I'm trying to configure my system to be able to show the last record in a table while entering a new record in the same table.

           The record that I want to be shown is the latest one, preferably based on a date field, but this isn't alfa omega, it could alternatively be based on the creation date of the record if this is easier.

           I have a table for each of Clients, Plants and Operations. Each client has several plants, and at each plant several operations are performed.

           When I enter a new operation, I would like specific data fields from the last operation performed at specified Client and Plant to be shown alongside the record currently in creation. 


           I tried creating a new table occurrence of Operations (Operations 2) relating them via Client ID and Plant ID and then adding a portal to the original one in order to show data from the new one, but this isn't possible at all. I can't even create one to show data from the Clients or Plants tables in the Operations table.

           Now I've setup calculation fields for each field I want to be show and set them to calculate for example, Last (Operations::volume), and set to calculate in the context of Operations 2.

           This seems to be working, but I have a feeling that it's is a really bad workaround that might come back and bite me as the database gets more and more complex, and I have a feeling that I'm trying to find the forest through the trees.

           Do you guys have any suggestions for a more simple and clean way of doing this?


           Thanks!

        • 1. Re: Showing last record while entering new record
          philmodjunk
               

                    I tried creating a new table occurrence of Operations (Operations 2) relating them via Client ID and Plant ID and then adding a portal to the original one in order to show data from the new one, but this isn't possible at all.

               That should be possible, though not the only way to do this. In an unsorted found set, the last record in the found set will be the most recently created records so if you performed a find for all Operations records for a specific Plant ID and then used go to record to go to the last record, you'd have, as a current record the most recently created Operations record for that plant.

               If your relationship is correctly set up, Last ( ) will return data from the last related record and this is the most recently created record unless you have specified a sort order for the relationship that makes some other record the last related record.

          • 2. Re: Showing last record while entering new record
            LarsChristensen

                 I have my relationship set up as follows, but I still can't add a portal. When the portal setup window opens the only working button is "Cancel".

            • 3. Re: Showing last record while entering new record
              philmodjunk

                   And you added it to a layout based on what table occurrence? Operations?

                   And where on the layout did you add it? You can't put one portal inside another so that's one possible explanation of what happened.

                   And, as previously discussed in another thread, you don't need to match by both client and plant ID if Plant ID is properly set up to be unique. You can just match by Plant ID.

                   And those links to personnel look like a real mess. That's another topic, but there are better ways to set that up.

              • 4. Re: Showing last record while entering new record
                LarsChristensen

                     Hah, yeah the personnel tables are a serious mess. What I need is a way to add up to eight different operators to each operation performed, and this is the only way I could crack it. Is there a simple way to mend this?


                     I'm adding it on the Operations table occurrence, and as for the placement, I'm not nesting it or anything like that, so that shouldn't be the problem.


                     By the way, I've removed the Client ID match in the relationship, so that's better now.

                • 5. Re: Showing last record while entering new record
                  philmodjunk

                       In relational database design, the solution is often to add in another table (or table occurrence) and set up a relationship. This is no exception. You can add a related table for logging the operators for that operations record.

                       Operations------<Operators>--------Personnel

                       A portal to Operators placed on your operations layout can be used to log each operator. Not only does thus reduce your relationships to a single link to personnel, you are no longer limited to a specific number. If a different plant requires more than 8 operators, you just add more records in the portal.

                       If you are adding a portal to Operations that you plan to set up for Operations 2, I see no reason why you would get the result that you have reported.

                  • 6. Re: Showing last record while entering new record
                    LarsChristensen

                         I'm sorry if this is obvious, but how would I then relate these tables? On which match field?


                         Thanks!

                    • 7. Re: Showing last record while entering new record
                      philmodjunk

                           Operations::__pkOperationID = Operators::_fkOperationID

                           Personnel::__pkPersonnelID = Operators::_fkPersonnelID

                           Operators is actually a join table linking Personnel to Operators in a many to many relationship. Data about that operator specific to that Operations work (maybe the line or machine being operated that shift?) can be logged in fields in the Operators table.

                      • 8. Re: Showing last record while entering new record
                        LarsChristensen

                             Alright, alright. Now I'm running facing the same problem as before; the inability to create a portal in any layout based on the operations table. This is a big problem.


                             I think I need to rethink this whole database a lot.

                             The problem is that my boss keeps getting more and more excited about the prospects of this program and how it can help streamline our data on the operations we perform.


                             Really shortly, this database is supposed to organize all the information of the operations we perform, and data entry is supposed to happen across about 10 different employees.

                             Once the specific clients and plants are entered in the system (we rarely get new customers, as we satisfy pretty much 100 % of the national market), the big picture is something along the lines of:


                             - Commercial proposal: Price of operation, volume to be handled, date, work hours etc.
                             - Operation details: Actual volume handled, equipment used, operators used and whether or not they worked day or night shift, etc.

                             And so on with more details about the specific operation.

                             Each person responsible for their part of the operation would then have a personal layout only accesible by them.

                             Would it be good to have a table for each of these layouts, and then link them via the contract ID? They might contain like 30-40 fields each, and right now I have them all in one table; the Operations table, which I feel might be a very bad idea.


                             Any help and advice is appreciated. Thanks!

                        • 9. Re: Showing last record while entering new record
                          philmodjunk

                               Before I can suggest anything, I need a much more complete understanding of the problem.

                               Can you upload a screen shot of what Portal Setup... looks like when you use it to try and add a portal to Operations 2?

                          • 10. Re: Showing last record while entering new record
                            LarsChristensen

                                 I'm not trying to add a portal to a layout based on operations 2, I'm trying to add it to a layout based on the original Operations table.

                            • 11. Re: Showing last record while entering new record
                              philmodjunk

                                   English is so imprecise sometimes. Yes, I meant that you are adding a "portal to operations 2" to your Operations layout.

                                   Can you confirm that you can't get the "Show Rleated Records from:" pop up to respond if you click on it? If that doesn't work, your file or layout may be damaged.

                              • 12. Re: Showing last record while entering new record
                                LarsChristensen

                                     " [...] if you click on it?"
                                     
                                     Wow. This was the solution. This was the actual solution. I need to get my shit together.
                                     
                                     
                                     Thanks again!

                                • 13. Re: Showing last record while entering new record
                                  LarsChristensen

                                       I'm having a bit of trouble with this Operators - Personnel solution.


                                       "Operations::__pkOperationID = Operators::_fkOperationID

                                       Personnel::__pkPersonnelID = Operators::_fkPersonnelID

                                       Operators is actually a join table linking Personnel to Operators in a many to many relationship. Data about that operator specific to that Operations work (maybe the line or machine being operated that shift?) can be logged in fields in the Operators table."


                                       How is it supposed to work?

                                       I've created the table and set up the relationships, but I still can't see how it's supposed to function. How do I pick which operators are performing each operation?

                                  • 14. Re: Showing last record while entering new record
                                    philmodjunk

                                         You would start by placing a portal to Operators on your Operations layout. A value list of Personnel can be set up for selecting an operator in the _fkPersonnelID field.

                                         I'm not sure what you are asking by this:

                                         

                                              How do I pick which operators are performing each operation?

                                         Your entire table is named "operations" so simply by listing personnel in the portal to Operators, you are selecting them for that Operations record.

                                         If, however, you mean that you need to document that different operators are performing different tasks, or operating different equipment, you can add fields to the Operators table and place them in the portal to use for that purpose.

                                    1 2 Previous Next