2 Replies Latest reply on Aug 31, 2015 11:45 AM by sccardais

    Filtered relationship question

    sccardais

      My database is used to manage our small Homeowners Association.

       

      I want to automate the process of sending invoices in July and February. Invoices are based on the number of lots each member owns. Most members own one lot but several own two or more. My table structure looks like this:

       

      Accounts

      Contacts with one contact designated as the "Primary"

      Lots

      Billable Items

      • ID
      • fk_ID_Account
      • fk_ID_LOT
      • fk_ID_Invoice
      • Fiscal Period
      • Description
      • Amount

      Invoices

       

      A script creates a set of "Billable Items" for each lot, fiscal period and dues or assessment item. e.g. The Description field in a "Billable Item" record might look like this, "Annual Dues for 2015-2016 for Lot# 2

       

      This adds 31 records (one for each lot) to Billable_Items. I took this approach to make sure a line item is created for every Lot.

       

      Now I want to create an invoice for every record in Billable Items that doesn't have a value in fk_ID_Invoice and I'm stuck.

       

      From a layout based on Accounts, I want to see a list of all Billable Items with a matching  ID_Account AND where ID_Invoice is blank. (e.g. not previously invoiced).

       

      I've tried using constant values  in Accounts to create a relationship with Billable Items but so far, that isn't working.

       

      Accounts::ID_Account = Billable_Items::fk_ID_Account AND Accounts::Constant ("INV") ≠ Billable_Items::fk_ID_Invoice.

       

      Every ID_Invoice states with "INV"

       

      From a layout based on Accounts, how can I view a list of Billable Items that match on ID_Account AND with nothing in fk_ID_Invoice?

        • 1. Re: Filtered relationship question
          erolst

          You can't match on partial values; what you can do is

           

          • use the regular relationship between Accounts and BillableItems and a portal filtered by:

           

          IsEmpty ( Billable_Items::fk_ID_Invoice )

           

          • create a calculation field in BillableItems, say, cAccountIDUnbilled as …

           

          Case (

            IsEmpty ( Billable_Items::fk_ID_Invoice ) ;

            Billable_Items::fk_ID_Account

          )

           

          … and base your relationship on Accounts::ID_Account = BillableItems__Unbilled::cAccountIDUnbilled

           

          Make sure the calculation's result type is “text” – since if your ID_Invoice starts with "INV", it must be a text field, and so must be the foreign key which you're referencing here as result.

          • 2. Re: Filtered relationship question
            sccardais

            Thank you.

             

            Through experimentation, I found another way. Using a constant of “1” in ACCOUNTS, I created a relationship with a new TO of Billable_Items as shown in screenshot below.

             

             

             

            In case the screenshot doesn’t appear above, this is the relationship.

             

            Accounts::ID_ACC = Billable_Items_Not_Invoiced

            AND

            Accounts::g_ONE-Constant > Billable_Items_Not_Invoiced::fk_ID_Invoice

             

            Is there anything “wrong” with this? It’s an “academic” question but I’m trying to learn.

             

            Your approach seems more logical and would be easier to decipher months from now. I’ll try it.

             

            Thanks.