1 2 3 4 Previous Next 58 Replies Latest reply on Apr 2, 2013 2:06 PM by JohhnyHilly

    Finding a previous record in portal that is based on a checkbox result

    JohhnyHilly

      Title

      Finding a previous record in portal that is based on a checkbox result

      Post

           I hope this can be done because I need to create a calculation from this.

           I have a layout with a portal on. This portal has Equipment Name, Total Running Hours fields and a checkbox with yes/no for Oil Change. A new record is created on a daily basis recording the total running hours and if the oil has been changed or not. 

           I need to write a calculation that determines the running hours since the last oil change, i.e. between "todays" record and the last record that had "Yes" checked in the checkbox in the portal under Oil Change. I'm thinking I need to search for this record, set the variable of the total running hours field and calculate the difference between them to find the result. I have no idea to go about this though so any help would be appreciated.

           Bear in mind, there will be multiple pieces of equipment in the portal so I'm not sure if that will make a difference in searching for that record.

        • 1. Re: Finding a previous record in portal that is based on a checkbox result
          philmodjunk

               I don't think you need a script for that, but you will need a relationship that matches only to records in the portal's table for the same precise item of equipment. This relationship can be used in a calculation to either copy the hours from the latest preceding record for that item of equipment or zero if the oil change check box is selected. A second field can then add the hours from this field to the hours recorded in the current portal record to give you a running total that resets with each oil change.

               There IS a way to get a summary field to reset with each sorted group, but this won't work with a mixed list of different equipment. I'm mentioning this because if you sorted your records by equipment, then by date, there might be a way to get this to work in a list view report rather than your portal and be much easier to set up.

          • 2. Re: Finding a previous record in portal that is based on a checkbox result
            JohhnyHilly

                 I'm all for doing the most simple method. I'm not actually sure which comes first, equipment or date. Referring to this post, http://forums.filemaker.com/posts/bbb98a9056, I have built an equipment list for a vessel, then that list is used in the new daily record (by date) of that equipment list, so I guess it goes by equipment then date?

                 Would you be able to run me through how I can do this (method 1 or 2). Again, I understand what you are saying, but I'm not sure how to setup that relationship you outlined.

                 I've attached my current relationship setup. The layout I am referring to is based on the MDR table, and the portal in that table is linked to Equipment_List through the table occurence Equipment_List2.

            • 3. Re: Finding a previous record in portal that is based on a checkbox result
              philmodjunk

                   Your relationship from MDR to Equipment_list should match by _fkVesselID shouldn't it?

                   Are you recording this data in the Equipment_List table?

                   If so, why isn't it being recored in one of the tables for Oil sampe?

                   What does one record in MDR represent?

              • 4. Re: Finding a previous record in portal that is based on a checkbox result
                JohhnyHilly

                      

                     Not entirely sure if it should be matching by _fkVesselID. How I have it seems to be working ok at the moment. Should it be related by the vessel ID?

                     An MDR "Machine Daily Report" record represents a daily record on the equipment built from the Equipment_List table. This includes "Running hours from last oil change", "Running hours until next oil change" etc, with like I said, a new MDR record for a vessel being created on a daily basis with the values updated based on the calculations I would like to write I mentioned in the original post.

                     This data is being recorded in the MDR table.

                     Even though Oil Samples are related to the Equipment, in that an oil sample is taken from the equipment, the oil samples aren't associated with the daily machine report, so that's why it isn't being recorded in the OIl Sample table.

                      

                      

                • 5. Re: Finding a previous record in portal that is based on a checkbox result
                  philmodjunk

                       Each record in Equipment_List should represent a single item of equipment installed on a specific vessel. There should not be multiple records for the same item of equipment in this table.

                       It sounds to me like you are attempting to record data collected daily in different records for the same item of equipment in Equipment_list and this is not a good idea.

                       So yes, it should match by vessel ID as the equipment list should provide a list of all equipment installed on a given vessel. A different table--which can be linked to records in the equipment list--should be used to record your daily data.

                  • 6. Re: Finding a previous record in portal that is based on a checkbox result
                    JohhnyHilly

                         That is how I have it setup, each record in Equipment_List is a single piece of equipment installed on a vessel. The number of records depends on which equipment is selected in the "checkboxes" from this post, http://forums.filemaker.com/posts/bbb98a9056?page=4

                    It sounds to me like you are attempting to record data collected daily in different records for the same item of equipment in Equipment_list and this is not a good idea.

                    But this was the idea of the equipment list. I was under the impression that if a new piece of equipment was added to Equipment_List through the Equipment table, then that piece of equipment is available to ALL vessels, and when selected from the 'checkbox', it is like allocating a seperate Main Engine Port (as an example) and thus new record to every vessel. So technically speaking for example, if there are 10 vessels, 10 port main engines are selected/present even though only 'one' Port Main Engine is in the equipment list. This would then allow data to be collected in different records (daily MDR records). I hope you understand what I am trying to explain. What should I do?

                    So yes, it should match by vessel ID as the equipment list should provide a list of all equipment installed on a given vessel. A different table--which can be linked to records in the equipment list--should be used to record your daily data.

                    This is why I have the MDR table. It is linked to the Equipment_List table through the table occurence Equipment_List2. So should the relationship read like this?

                    MDR::_fkVesselID = Equipment_List2::_fkVesselID

                    • 7. Re: Finding a previous record in portal that is based on a checkbox result
                      philmodjunk
                           

                      I was under the impression that if a new piece of equipment was added to Equipment_List through the Equipment table, then that piece of equipment is available to ALL vessels,

                      This is not the case. A Equipment_List was intended to be a "join" table linking a specific piece of equipment from a master list of such items to a specific vessel. If Vessel 1234 has a winch of a particular make and model, there is a record in Equipment_list with the the Equipment_ID for that specific type of winch and the Vessel ID for that specific Vessel.

                           And, for a given vessel, there should only be one record in Equipment_List for that specific winch on that specific vessel.

                           

                      So technically speaking for example, if there are 10 vessels, 10 port main engines are selected/present even though only 'one' Port Main Engine is in the equipment list.

                           Nope, There would be 10 records in Equipment list with 10 different vessel ID's but the same equipment ID--that links them all to the same record in your master equipment list.

                      • 8. Re: Finding a previous record in portal that is based on a checkbox result
                        JohhnyHilly

                             Ok that is not what I needed to hear. Sigh.

                             How am I able to achieve this? Create master generic equipment list accessible to all vessels, that once a piece of equipment is checked/selected to be part of a vessel equipment list, then it creates it's own ID number/record so I can then go and add extra information specific to that piece of equipment like make and model. This is what I have wanted the whole time but I clearly wasn't understanding the purpose of this join table.

                             Can I do this with my current table structure and relationships? I am really hoping so.

                        • 9. Re: Finding a previous record in portal that is based on a checkbox result
                          philmodjunk

                               Ok that is not what I needed to hear. Sigh.

                               It's what I suggested from the beginning. The purpose of the master list is so that information specific to a particular make and model need only be entered into your database once instead of repeatedly.

                               Information specific to that piece of equipment's installation on that specific vessel would be recorded in Equipment_list. Things like Total Hours in service, date installed, date of last service... would be examples of fields in Equipment_List. If, however, you are needing to keep a series of items of data on that piece of installed equipment such as a service history, you need yet one more table linked to Equipment_list for logging that data in a series of related records.

                               What you should have and what I thought that I had diagrammed out for you in previous threads is:

                               Vessels-----<Equipment_List>------Equipment

                               Vessels::__pkVesselID = Equipment_List::_fkVesselID
                               Equipment::__pkEquipmentID = Equipement_List::_fkEquipmentID

                               For the relationships that you have posted here, this simply requires adding on one more TO for Equipment linked to the far side of your Equipment_List TOs.

                               To add in a "service History" table, you could do this:

                               Equipment_List-----<ServiceHistory

                               Equipment_List::__pkEquipment_LIstID = ServiceHistory::_fkEquipment_ListID

                          • 10. Re: Finding a previous record in portal that is based on a checkbox result
                            JohhnyHilly

                                 Yes that is how I have it setup, based on your previous posts, but I'm not quite sure you understand what exactly I am trying to do. Either that or I am really confused.

                                 I don't want to enter the make and model into the Equipment table at the start of the data input process. There are numerous vessels with numerous makes and models of all types of equipment that will make the checkbox list too long and wouldn't be the best way to select/check equipment in the list.

                                 What I would like is to enter the equipment name e.g. Main Engine Port into the Equipment layout/table. This is a generic equipment item that will now be in the master list (checkbox list) avilable for all vessels when the equipment list is being built. If I want to include this Main Engine Port on a specific vessel I will check it in the checkbox, then I want it to create an ID number for that Main Engine Port on Vessel 1234, and it will be here where I enter the make and model etc.

                                 Does that makes sense? With my current setup am I able to do this? If so how? frown

                            • 11. Re: Finding a previous record in portal that is based on a checkbox result
                              philmodjunk

                                   Conceptually, in terms of tables and relationships, that works the same as what I just described, but with a shorter list  of records in the Equipment table. The relationships would be the same, you'd just end up with more fields in the Equipment List record--including some that will contain duplicate data in cases where you have the same make and model of a piece of equipment listed more than once such as when it is the same on two different vessels. That seems a bit inefficent, but isn't my main concern.

                                   My concern was that you not enter "service history" type data into the Equipment List table in such a way that you end up with multiple records for the same piece of equipment in an attempt to record ongoing maintenance tasks.

                              • 12. Re: Finding a previous record in portal that is based on a checkbox result
                                JohhnyHilly

                                      

                                     Ok thanks for the confirmation, I want to avoid any duplicate data...but a Port Main Engine on one vessel would be considered different to the Port Main Engine on another, even if it is the same make and model. Would it be considered duplicate data if they are on different vessels?

                                     With regards to your suggestion on the "Service History" table, yes I will add that thanks, but could you please clarify, what data should be kept in the Equipment_List table and what would be keep in Service History? (Sorry if you feel you are repeating yourself, I just need to get this right). Would I keep all records relating to the daily machine reports in the service history table (i.e. a new records generated every day)? Like Total Running Hours etc?

                                • 13. Re: Finding a previous record in portal that is based on a checkbox result
                                  philmodjunk
                                       

                                            Port Main Engine on one vessel would be considered different to the Port Main Engine on another, even if it is the same make and model.

                                       Which is why they would be separate records in the equipment list table, each with a different VesselID and they would not be duplicate records by the very fact that they are part of different vessels. They can, however, link to the same "port main engine" record in Equipment.

                                       

                                            what data should be kept in the Equipment_List table and what would be keep in Service History?

                                       I'll answer your question with more questions because while I know Databases and FileMaker, you know the industry for which you are creating this database.

                                       Will the data you are recording on a "port main engine" for example, be recorded once for all Port Main Engines?

                                       If yes, this is data for the Equipment table.

                                       Will the data you are recording be recorded once for each "port main engine" on each vessel?

                                       If yes, this is data for the Equipment_List table.

                                       Will the data you are recording be recorded multiple times for each port main engine on a given vessel? (and you want to keep all the data, not just the most recent data)

                                       If yes, this would be recorded in the Service History table and linked to a specific record in the Equipment_List table.

                                  • 14. Re: Finding a previous record in portal that is based on a checkbox result
                                    JohhnyHilly
                                         

                                    Which is why they would be separate records in the equipment list table, each with a different VesselID and they would not be duplicate records by the very fact that they are part of different vessels. They can, however, link to the same "port main engine" record in Equipment.

                                         Do I need to create a new record ID for each time a piece of equipment is assigned to a vessel? Or with my current setup have I already achieved this by adding the piece of equipment to an equipment list?

                                         

                                    I'll answer your question with more questions because while I know Databases and FileMaker, you know the industry for which you are creating this database.

                                    Thanks, everything is much clearer with this. Just FYI, by adding my "MDR" table (see earlier in the post) I wanted this table to do exactly the same as your "Service History" table.

                                    Following on from above, I have now having one more small issue relating to the setup of a layout and portal. I'll explain:

                                    1. Vessel Equipment layout: Here I am selecting the equipment from the "checkbox" master list that is built from the Equipment table.

                                    2. I click a button and it takes me to Vessel Equipment Specifications layout. On here is a portal linked to Equipment_List table. In this portal, I have information such as make and model and sump quantity. This data coinsides with your 2nd answer above, in that I am recording it once for each "port main engine" on each vessel. So far everything is fine.

                                    3. I then click a button to go to another layout, MDR. Here I need to have a portal so it is setup that the equipment list is there, but now I want to record multiple times for each piece of equipment in that list on a given vessel (conincides with your 3rd answer above) with info such as Total Running Hours and Oil Change - Yes/No. So this data is recorded in the Service History table.

                                    If I link the portal to Equipment_List, the list will come in, but if I enter a value for Total Running Hours for for example, Port Main Engine, it will apply this value to the rest of the pieces of equipment in that list. If I link it to Service History, it won't bring in the equipment list I have built for that vessel. Does the layout need to show records from a certain table? How can I achieve what I just described?

                                    BTW, when you said "If yes, this would be recorded in the Service History table and linked to a specific record in the Equipment_List table.", how do I link it to a specific record in the Equipment_List? Is this where I am going wrong?

                                    1 2 3 4 Previous Next