14 Replies Latest reply on Nov 20, 2016 7:11 AM by xxx202xxx

    Condition formatting calculation

    xxx202xxx

      I have an asset_table and history_table where I am copying key fields from a record from the asset table over to the history table.  Once the information is copied nothing else happens on the history table (archived).  If for any reason the copied record from the asset table get modified and a new copy is generated over to the history table I would like to be able to see which fields changed between the two copies by having the changed fields formatted by a color or something.

        • 1. Re: Condition formatting calculation
          philmodjunk

          Do you have one history record per asset record or, as I assume is the case, multiple history records for a given asset?

           

          With multiple history records, you'd want to compare values in the asset record to only corresponding values in the most recently added history record. This can be one either thru use of the Last record or via a sorted relationship that sorts the records in descending order by either an auto-entered serial number or an auto-entered timestamp. That will make the most recently created, related history record the first related record.

           

          So if you have this relationship:

           

          Assets::__pkAssetID = History::_fkAssetID

           

          you could use this relationship:

           

          Assets::FieldA <> Last ( History::FieldA ) ---> using an unsorted relationship

           

          or

           

          Assets::FieldA <> History::FieldA ----> if relationship is sorted to make the most recent History record the first related record.

          • 2. Re: Condition formatting calculation
            xxx202xxx

            The fieldA stays filled no matter what the value is.  I have that relationship, I have it sorted. If fieldA does not change but this time fieldB does I want fieldB to get filled and I do not want fieldA to be filled.  I only want the field that do change.  I don't know if I'm doing something wrong or not.

            • 3. Re: Condition formatting calculation
              philmodjunk

              We are talking conditional format calculations. I provided and example where a single field would change fill color if it's value does not match the corresponding field in the related history record. For multiple fields, you would replicate this calculation as a conditional format calculation on each field--but with different field references are you are comparing different fields each time.

              • 4. Re: Condition formatting calculation
                xxx202xxx

                I see what's going on now.  What you suggested worked but I guess I did not explain myself correctly.  The reason why I was not seeing the filled in color was because the statement Asset::FieldA <> Last (History::FieldA) was not showing the change on the Last Record committed because both fields were equals.  By changing the formula to  Asset::FieldA = Last (History::FieldA) then the Last Record on the History layout color was filled in because it no longer matched the current Asset:::FieldA record but was different than the record prior to the last record.

                • 5. Re: Condition formatting calculation
                  xxx202xxx

                  I needed to see the differences between the Last record and prior to the last recordl

                  • 6. Re: Condition formatting calculation
                    philmodjunk

                    What you have posted makes no sense.

                     

                    If you want to see a color change in the field when the value in the field is different from that of the same field in the most recent history record, you would use the conditional format expression that I recommended.

                     

                    Checking to see if the values are = will produce a color change if the values are the same, not when they are different.

                     

                    Please read my prior posts again. If you use a sorted relationship, do not use the last function. Only use that if your relationship is UNSORTED.

                    • 7. Re: Condition formatting calculation
                      xxx202xxx

                      I've tried both ways. How can I simply compare the same field from 2 different records from the same table and have the last record colored.  This is so confusing

                      • 8. Re: Condition formatting calculation
                        philmodjunk

                        Download this file and compare it to what you've tried. I've set it up with just two fields. FieldA and FieldB. Either fills with bright yellow if the corresponding, most recently added history record does not have the same value in the field of the same name.

                        • 9. Re: Condition formatting calculation
                          xxx202xxx

                          I don't see the file

                          • 10. Re: Condition formatting calculation
                            philmodjunk

                            You won't see it in your email application nor if you open it from the forum's 'in box". You have to open this discussion in a web browser to see the download link. If you are looking at this in the forum's in box, click the Title of your first post.

                            • 11. Re: Condition formatting calculation
                              xxx202xxx

                              can  you put  it in a fp7 format.  I have FM 11

                              • 12. Re: Condition formatting calculation
                                philmodjunk

                                That pretty much requires starting over from scratch. I can do some copy and pasting. I'll also have to switch computers as I don't have a version that old on this machine.

                                 

                                In the future, if you are not using the most recent version of FIleMaker, please let others know in your first post. Not only can this happen, but you might get advice on how to do something that does not work with your version of the software.

                                • 13. Re: Condition formatting calculation
                                  philmodjunk

                                  Here's the .fp7 version of this file

                                  • 14. Re: Condition formatting calculation
                                    xxx202xxx

                                    Thanks.  I finally got it to work I guess what I didn't understand or what I was thinking was that I wanted to see the current history record filled in.  I had set up a portal similar to what was in your zip file to notice it. So by committing the record nothing was filled in but in the portal everything that was changed when looking at the previous record was filled in.