5 Replies Latest reply on Jan 9, 2012 9:09 AM by philmodjunk

    I'm Trying to write to another table

    weedonpaul

      Title

      I'm Trying to write to another table

      Post

      Hi

      In order to get an invoice number for a report i have written a little script to go to a table invoice and check if one already excist and if not create one, but it doesn't work what am i doing wrong? i have attached my script.

      Edit_Script__sort_billing__%28Customers%29%2A.jpg

        • 1. Re: I'm Trying to write to another table
          philmodjunk

          I can see several issues and can imagine more--depending on your layouts and relationships.

          What relationship exists between Invoices and TimeSheet?

          On a layout to what table occurrence does this script start? Invoices or Timesheets?

          I think you are running this script on a timesheet layout. If so, this expression doesn't look like it will work for you:

          If [ (timesheet::Company ...

          The expression will be true only if a) either is no related invoices record, or the related invoices record has a different company name) AND

          b) The month fields do not match between the related records AND

          c) The year fields do not match.

          I would think you would want to use OR here so that if any of the three tests faile, you'd go ahead and created the new record.

          Then, you are using Set field by Name steps were it would seem you need set field steps instead. (The steps, as written with set field by name would require that Company, Month and Year fields in Invoices be text fields that store the table::FieldNames of three fields.)

          • 2. Re: I'm Trying to write to another table
            weedonpaul

            I have change the script now and have got it to work, but if you could cast your eye over it and offer comments that would be great

            the relation ship between invoice and timesheet is company, month, year. i did have the script start at layout enter, but that was problematic so i have a button.

            I think i need to use "and" but i see no reason why "or" wouldn't work either, (this has got my head spinning).

            • 3. Re: I'm Trying to write to another table
              philmodjunk

              I think i need to use "and" but i see no reason why "or" wouldn't work either, (this has got my head spinning).

              Since you are finding TimeSheet records and looking for a matching record via the relationship to Invoice 2, you can simply check any one of the three fields (or any other field in INvoice that must contain data) for the presence of data, you don't even need to check to see if it is equal. If there is no related record any reference to the related table will return nothing. You could just use: IsEmpty ( Invoice 2::Company ).

              Additional comments:

              1) You will be better off using Set field intead of insert calculated result. The "insert" script steps will silently fail to work if the target field is not physically present on the layout. Set Field will not have this limitation so it is a bit more "robust".

              Any reason why you didn't use Set Field as I previously recommended?

              2) What criteria are you using to find invoice records in order to update the date field. Why are you updating multiple invoice records like this?

              3) the relationship between invoice and timesheet is company, month, year.

              That would seem to indicate that you have this relationship:

              Invoice 2---<TimeSheet

              Invoice 2::Company = TimeSheet::Company AND
              Invoice 2::Month = TimeSheet::Month AND
              Invoice 2::Year = TimeSheet::Year

              If, in this relationship, you enable "Allow creation of records via this relationship." for Invoice, you can replace your script with this single step, if performed from a layout based on TimeSheet (TimeSheet must be listed in Show Records from in Layout Setup), with a timesheet record with the correct company, month and year as the current record, a single script step can be used tocreate a new related invoices record, if one does not already exist:

              Set Field[Invoice::Company ; TimeSheet::Company]

              If the related record does not already exist, one is created. If one exists, the related record's company field gets the value stored in Timesheet::Company--which is the value already stored in that field, so no change takes place.

              You should not match records in a relationship of this type by company name. Company names are not unique and they change from time to time. If you base a relationship on a company name, both doing business with a second company of the same name or situations where a company name changes will cause problems for you. Use an auto-entered serial number to identify your company instead of the name. (your system can be set up so that users select a company by name, but enters the ID number.)

              Assuming that you have Date fields in both tables, you can also simplify the relationship to be:

              Invoice::CompanyID = TimeSheet::CompanyID AND
              Invoice::Month = TimeSheet::cMonth

              Where Invoice Month is a date field and cMonth is a calculation field that returns month using this calculation:

              TimeSheet::Date - Day ( TimeSheet::Date ) + 1

              Which returns the date of the first day of the month to give all timesheets for the same month a common value and which also includes the year, thus eliminating one pair of fields from your relationship.

               

              • 4. Re: I'm Trying to write to another table
                weedonpaul

                Any reason why you didn't use Set Field as I previously recommended?

                I did originaly but whilst trying to sort out problems i tried insert calculated field and with a few other changes it worked so i left it as is.

                As for your other suggestions I will have a play later and try to simplify the script.

                I take your point about customer name, as we have already had one change their name do you have any suggestions on a quick way to impliment a change over. i have already added a field in the table customer.

                many thanks

                 

                • 5. Re: I'm Trying to write to another table
                  philmodjunk

                  I did originaly but whilst trying to sort out problems i tried insert calculated field...

                  Switch to set field--not set field by name for these steps. They are less vulnerable to trouble should you modify your layout at some point in the future and accidentally remove one of these fields.

                  do you have any suggestions ...

                  It will take some work. Once you have added the serial number field, you can use replace field contents with the serial number option to give all your existing customer records a serial number. Then you'll need to update your database layouts to work correctly with the serial number in place of the name. The basic "first step" approach is to use value lists where the ID number is listed in "field 1" of the value list and the name (or name pluss additional info such as an address) is listed in the second field. That allows users to select a customer by name, but the value list enters the ID number. Once you can get that working, you may want to move up to enhanced, script supported methods that make it easier to search out a customer by name, but still enter the ID--with code that handles duplicate names correctly.