5 Replies Latest reply on Aug 14, 2013 10:36 AM by philmodjunk

    Need help please: update current record from previous record

    JennySemmler

      Title

      Need help please: update current record from previous record

      Post

           i am not quite a newbie at FMP, but not particularly advanced either. Can I please have some assistance with the following - I need to do some complex calculations and don't know whether my database is set up right. 

           To calculate wine blends:

           One wine blend has many operations.

           A transfer operation has an EX tank, initial volume and final volume for a BLEND ID and a TO tank, initial volume and final volume for a BLEND ID (which may be the same or different, and will be manually assigned).  The transfer operation may also be a despatch or bottling operation, in which case there is no To tank record. 

           If I assign an EX TANK, I need to find the previous record containing that tank in the TO tank, and look up the To Final Volume AND the To Blend code. If EX blend ID in the current record matches TO Blend ID in the previous record, I need to copy the volume to the Current EX tank initial volume, otherwise run a warning script asking whether the transfer will proceed because the blends are different. Can I nest logic commands?

           I am currently using a portal to test the operations to the blend code, but eventually once working I would prefer to open a new window to enter the operations as this will be more user friendly. 

           Can you please assist me with the structure? I have created a duplicate table for selfjoin but don't quite know how to use this. Is this how I should proceed? If so, I'm a little lost as to the formulae I should use. 

           Your help would be appreciated. 

           Thank you

            

      Screenshotdatabase.png

        • 1. Re: Need help please: update current record from previous record
          philmodjunk
               

                    If I assign an EX TANK, I need to find the previous record containing that tank in the TO tank,

               I gather that you need to find a record in the Simple Operations table?

               And this needs to be the "previous record" where the TO tank matches teh EX Tank of the current record?

               And what data in your table identifies the "Previous Record"? (I wouldn't rely on the unsorted "creation order" to find the previous record. If you have to import the data--say into an upgraded copy of your database, the order in which you import the records becomes the new unsorted "creation order" and this may be a different order than what was in place originally.)

               Do you have an ID field in Simple Operations that auto-enters a serial number?

          • 2. Re: Need help please: update current record from previous record
            JennySemmler

                 Thank you for your answer Phil. 

                 Yes, I am using the operation number to index on an auto-entered serial number, because between say Apr & May I may do 100+ other operations. 

                 My search function will use TANK and BLEND CODE as a dual key, and the filter will be the maximum operation number (becasue we may take a wine out of tank and put it back into the same tank later). 

                 One question you may be able to help me with: for a self join, do I (a) create an entirely new table called Simple OPeration Self-Join, complete with auto0serial indexing or (b) duplicate the original table in >relationships so that it doesn't appear in the tables list? Reading through other posts on self-joins, this is not very clear. In my pic above this was a table I made that was exactly the same as the orginal, minus auto-serial numbering (option a). Since then I have tried experimenting with option (b), but don't want to invest too much effort into a method that is incorrect. 

                  

            • 3. Re: Need help please: update current record from previous record
              philmodjunk

                   a) is not a self join, it's a join to a completely different table. You want to do b) which is that you duplicate the Tutorial: What are Table Occurrences? in Manage | Database | Relationships and set up your links to it. You can then sort that relationship by the serial number field in descending order and then the first related record can be the previous record with the same matching data. You'll need to use  ≠ to keep that from being the same record instead of the previous record.

              • 4. Re: Need help please: update current record from previous record
                JennySemmler

                     Thank you! That explains a lot! A piece of poor user documentation I think. I'll bumble around with this for a while, but not close off the enquiry yet until I get it working. My next challenge is to get the script to find the right found set.

                     Cheers

                     Jenny

                • 5. Re: Need help please: update current record from previous record
                  philmodjunk

                       I wouldn't close the thread, ever. You never know when someone else will access this thread and have a useful suggestion to make.