12 Replies Latest reply on Aug 24, 2016 1:28 AM by globe11123

    Letters - Companies with multiple location

    globe11123

      Every month we send out letters to remind customers that the products they have purchased of us are due for refurbishment.

       

      I've created a letter which prints out all of the companies that are due for a certain month and year. This lists whatever units they have including serial numbers etc.

       

      Problem: Some companies have more than one depot or have multiple addresses, I am only able to currently print out whatever units are due that month for the company for 1 address.

       

      Units table / Units#Location - which includes serial number, product code etc.

      Customers_Units table - standard company information. (The Letter only looks at the one address from the customer. "RefurbLetterAddressIDFK")

      Addresses#Refurb_Letters table - multiple addresses can be stored in here for customers/

       

      Customers is linked to units by a field called last known owner which is basically a temporary field which gets updated by a script every time a unit comes back for refurb. Addresses is linked to customers via Customer_IDFK.

       

      My letter has a sub-summary which is stored by Customer_ID.

       

      On units I have a field called Location which is basically address line 3 i.e London. Maybe this could be used as a join between units and an address?

       

      How would I be able to change this so that it prints a letter out per address per company?

       

      address 3.png

      Figure 1: Multiple locations 1 company.

       

      letter.png

      Figure 2: All units printed for 1 address even though it would be 2.

       

      letter.png

      Figure 3: Relationships

       

      Any help will be much appreciated