1 2 Previous Next 25 Replies Latest reply on Nov 30, 2012 1:45 PM by paulatkins

    Auto-enter calculation

    paulatkins

      I am trying to auto-enter into a field that exsists in a related "Images" table where the CustomerID is the link in the relationship between the "Customer" table and "Images" table.

      The related table "Images" can have several records where the CustomerID is the same, because one customer can 'own' several images.

      The layout I use to enter this data uses a portal to the "Images" table, new records are entered in the "Images" table via this portal.

      I want to have a field in the Images table (P7) where the contents are auto-entered, but only once for each CustomerID.

      I have an auto enter calculation that does not work:

       

      P7= If ( CustomerID ≠ CustomerID ; "Address Label Print" )

       

      I want the words "Address Label Print" to be entered in field P7 on only the first instance of a CustomerID.

       

      Can anyone please offer some advice. Thank you in anticipation.

        • 1. Re: Auto-enter calculation
          paulatkins

          I just realised, how can that calculation work when I need to refer to another record.

           

          P7=  If ( CustomerID  ≠ CustomerID ; "Address Label Print" )

           

          So I need the calc to look at all records and the field "CustomerID", is that possible?

          What would that calculation look like?

           

          Sorry for not thinking that through before posting.

          • 2. Re: Auto-enter calculation
            comment

            There are a few things here that do not fit. First, "the  first instance of a CustomerID" (I guess that means the first related record in the Images table) is not - or at least, should not be - significant in any way. Anything you want to have only one instance of should go into the "one" record in the parent Customers table.

             

            The other thing is that all related records have of course the same CustomerID value, so you cannot tell them apart by this kind of test. There is a way to identify the first related record by using a self-join - but I think it would be best if you explained what exactly are you trying to accomplish here first.

            1 of 1 people found this helpful
            • 3. Re: Auto-enter calculation
              paulatkins

              Hi Michael, thank you for responding.

               

              Each customer has one or more images associated with them, hence one table for customers, and one table for images.

              When a customer is photographed, their details are entered into the Customer table, and in the images table portal (a part of the layout) the selected image file number/name is typed in as well as the associated 'product' (part of a value list) and 'quantity' of that product.

               

              The images table holds that chosen image number, product and quantity. There are other fields in the images table that we use to feed another system, and these fields are auto-entered, they respond to what products and quantities are chosen.

               

              One of these fields needs to populate once for each customer (as it will be a part of producing an address label for the order). The system that this table will connect to requires that field P7 has the words "Address Label Print" in that field, but only once per CustomerID.

               

              At the end of the day, the data is exported as a .csv, with all of the fields set up properly for a print system that requires strict formatting. This last part i have sorted.

              • 4. Re: Auto-enter calculation
                comment

                paulatkins wrote:

                 

                The system that this table will connect to requires that field P7 has the words "Address Label Print" in that field, but only once per CustomerID.

                 

                Can you elaborate on this part? Is this required only for the file exported from the Images table or does it serve some other purpose as well?

                • 5. Re: Auto-enter calculation
                  paulatkins

                  Those words "Address Label Print" correspond to a product in another database, and them being in the P7 field will trigger an event once it is exported from FM and imported into the other software, so it needs to be there on export only, it is not neccessary for the FM db to have this.

                   

                  For neatness and my own education only, I'd like to see it in the FM db.

                  • 6. Re: Auto-enter calculation
                    comment

                    Try =

                     

                    Case ( CustomerID ≠ GetNthRecord ( CustomerID ; Get ( RecordNumber ) - 1 ) ; "Address Label Print" )

                     

                    The calculation must be unstored.

                    • 7. Re: Auto-enter calculation
                      paulatkins

                      No luck, that just produces "Address Label Print" in P7 of each record associated with the CustomerID.

                      • 8. Re: Auto-enter calculation
                        comment

                        Did you sort the records by CustomerID?

                        • 9. Re: Auto-enter calculation
                          paulatkins

                          Yes they are sorted by CustomerID. I sorted and tried again and got the same result.

                          I have no sort built in, but as a new record is added etc the CustomerID field in the Customer table is an auto-enter, incrementing number, so I believe it stays in this sort, it hasn't resorted throughout the testing all day.

                          • 10. Re: Auto-enter calculation
                            comment

                            paulatkins wrote:

                             

                            the CustomerID field in the Customer table is an auto-enter, incrementing number, so I believe it stays in this sort,

                             

                            I thought we were talking about the Images table. Does the attached work for you?

                            1 of 1 people found this helpful
                            • 11. Re: Auto-enter calculation
                              paulatkins

                              Yes your file does work, but the same does not work in mine.

                              Perhaps the problem lies in how that CustomerID arrives in my Images table.

                              The CustomerID is an auto-enter sequential number in my Customer table and in the Images table the CustomerID is arrived by the calculation:

                               

                              CustomerID= Customers::CustomerID

                               

                              I have attached the file, (although I am working in V12)  so you can see my thinking. Thanks Michael.

                              • 12. Re: Auto-enter calculation
                                comment

                                Which fields are you using as matchfields in the relationship between Customers and Images?

                                • 13. Re: Auto-enter calculation
                                  paulatkins

                                  CustomerID is the only matchfield.

                                  • 14. Re: Auto-enter calculation
                                    comment

                                    I am afraid I cannot open v.12 files yet. In any case, the CustomerID field in Images should not be a calculation, just plain Number (or Text) field. Otherwise the relationship cannot work, since it's based on circular logic.

                                    1 2 Previous Next