3 Replies Latest reply on Jun 26, 2013 9:24 AM by philmodjunk

    Relationship Trouble

    obidon

      Title

      Relationship Trouble

      Post

           Hi There,

           The attached is a first pass at relationship structure for a business management tool my client requires. In essence the client creates a simple forecast with few simple fields.  

             
      •           Date
      •      
      •           Description
      •      
      •           Amount
      •      
      •           Supplier (Table)
      •      
      •           Category (Table)

           Invoices come in and are entered into an actuals table which also has a few simple fields

             
      •           Supplier ID (Table)
      •      
      •           Category ID (Table)
      •      
      •           Forecast ID (Table)
      •      
      •           Date
      •      
      •           Description
      •      
      •           Amount
      •      
      •           PDF

           The system ties all components together in a simple, easy to use system. Not the most complex but very effective at managing against budget.  My issue is reducing the number of choices in the popup menu which populates Forecast ID in the Invoices/Actuals table.  

           I am using a related popup. I can do this by filtering 'forecast items using categories' or by  filtering 'forecast items using supplier'.  But I can't do both.  

           I have in the past filtered using a Grandparent, Parent, Child Relationship.  But that is not my structure here.  I can see that the issue lies because there is no link between catergories and suppliers but I can't fathom the nature of this relationship.

           All help greatly appreciated

      Screen_Shot_2013-06-25_at_16.16.14.png

        • 1. Re: Relationship Trouble
          philmodjunk

               Let me make sure that I understand your filtering logic here.

               If you select a Supplier in Actuals::SupplierID, how does that filter the list of forcast records? Limits it to only those forcase records with that SupplierID?

               And if you select a Category in Actuals::CategoryID, does that limit forcast recors to only those with that specified categoryID?

               And will the user be required to specify both a Supplier and a Category before selecting a Forecast?

               If so, then you need this relationship:

               Actuals::SupplierID = ForeCast|PopUp::SupplierID AND
               Actuals::CategoryID = ForeCast|PopUp::CategoryID

          • 2. Re: Relationship Trouble
            obidon

                 Thanks Phil

                 Your assessment of the logic is correct.  I want to refine my line item popup by both Category and Supplier.

                 Please can you explain the suugested relationship in a little more detail. The | character threw me.

                 Do I need to create a table with the two fileds you suggested?

            • 3. Re: Relationship Trouble
              philmodjunk

                   The | character is just a character. It has no functional effect on the relationship. In my example, ForeCast|PopUp is simply the name of the table occurrence box in the relationship graph. It could just as easily be: ForeCast PopUp or ForeCast_PopUp.

                   This is just another Tutorial: What are Table Occurrences? of your Forecast table.