1 2 Previous Next 17 Replies Latest reply on Mar 21, 2014 5:20 AM by willrollo

    Check data in related records

      Title

      Check data in related records

      Post

           I want to be able to trigger a print script depending on certain data in a related invoice table. 

           In essence - Customers - invoice details.

            

           I have a field, invoicedetails::date. 

           From the customers table, I want to be have a dialogue appear with a warning if any of the related invoicedetails:date fields are 14 days or less before the current date. 

           Not really sure how to go about this - am quite sure it is a simple answer but have brain fog.

            

        • 1. Re: Check data in related records
          philmodjunk

               Which means that you want to see if there are any records where the invoice date is less than or equal to Get ( CurrentDate ) - 14

               You can set up an unstored calculation field set to return date with that calculation and use it as an additional match field in a relationship between Customers and a new occurrence of invoiceDetails.

          • 2. Re: Check data in related records
            philmodjunk

                 And that Match field would use < instead of = in the relationship.

            • 3. Re: Check data in related records

                   Thanks Phil

                   I shall try it out and let you know if I have any issues...

              • 4. Re: Check data in related records

                     Just to confirm, I need to create this new field in the customers record and the invoice details record?

                     Trying to get my head around how it works.. could you briefly explain if you have a moment?

                     Huge thanks

                • 5. Re: Check data in related records
                  philmodjunk

                       Just the customer's record and I am not sure If I have specified exactly what you need. I might have guessed wrong so check things. This calculation field would match to the date field that you already have in InvoiceDetails.

                  • 6. Re: Check data in related records

                         Will that mean that this new field will be conintually overwritten, or is this why there is a table occurance of invoice details\?

                          

                    • 7. Re: Check data in related records
                      philmodjunk

                           It's an unstored calculation. As the date changes, it will update accordingly.

                      • 8. Re: Check data in related records

                             A little stuck..

                             I have created a new T.O. Called customers_INVOICEDETAILS_quote.

                             I have created a new relationship between the two, linked by the ContactPK =invoice details fk.

                             In this same relationship box, i have added a <  match field between the new customers:quotedate field and the invoicedetails:date field.

                        There is how an eggtimer on its side type icon on the diagram.

                        The calculated field, customers::quotedate has the properties: Get(CurrentDate)- Customers_INVOICEDETAILS_quote::Quote Date  ≤  14

                             from context of customers...

                              

                             I know the above is wrong as it doesnt work- pretty sure it is the calculated field part..but that is the extent of my attempt to remedy!

                              

                        • 9. Re: Check data in related records
                          philmodjunk

                               You can't use a reference to the related table in the calculation for a match field  to the same related table.

                               I recommended:

                               Get ( CurrentDate ) - 14

                               For that unstored calulation field.

                          • 10. Re: Check data in related records

                                 Hi Phil

                                 Thank you. I have altered the calc field (set as date) to Get ( CurrentDate ) - 14.

                            Now, when I create a new invoice in invoicedetails, every customer in customers table contains today's date  less 14 days in the field

                            Customers::Quote Date.

                                  

                            Is this the expected result? What I want to see is that if any related Invoicedetails::quote date records are less then 14 days from current date (i.e. 14 days or more recent) then the print script I want to use will come up with an error message...

                            So maybe a dont need a calculation of Get ( CurrentDate ) - 14, as that can be in my script to test how old/new the most recent invoice is????

                            Thank you

                                  

                            • 11. Re: Check data in related records
                              philmodjunk

                                   That is the expected result. You will recall that I said that I was guessing as to what you needed and that you should check the results.

                                   So with Today's date of March 14, you'd want all records from March 1st to Today, correct?

                                   Assuming that you have no records with a date in the future, you need all records where the date on the invoice is greater than or equal to the date of 14 days earlier than today.

                                   Customers::QuoteDate < InvoiceDetails::Date

                              • 12. Re: Check data in related records

                                     Hi Phil

                                     Sorry for late response.

                                      

                                So with Today's date of March 14, you'd want all records from March 1st to Today

                                Yes - If there are any invoice records related to the customer record I am viewing, then I would like a positive result in the new customers record (customers::quotedate) so that I can present a dialogue to the user warning him that the invoice he is about to chase is less than 14 days old.

                                At the moment, every customer record has the 4/03/2014 in the new customer field. I would have thought that the field will only have data in if this new field is less than or equal to the related invoice details record, minus the 14 days we entered as part of the calculation...

                                Have I made a mistake somewhere or has there been a miscommunication on my part do you think? Both equally likely!

                                • 13. Re: Check data in related records
                                  philmodjunk

                                       This is only a match field for a special purpose use. It should have the same date in every record as it is designed to calculate a date from the current date as set by your computer's system clock. To see the data you are looking for use the relationship that includes this field as a match field to check the data from the related table occurrence.

                                  • 14. Re: Check data in related records

                                         I read your post again on table occurrences to refresh my memory...All working now - thank you

                                    1 2 Previous Next