I've made some great strides with my invoicing solution for the local rental inspection department - Comment, bcooney and others have been extremely helpful!

I think I'm in need of a nested IF statement, or a case function, in a script, but I'm having a bit of trouble wrapping my mind around the logic.

The script I'm working on takes a group of dwelling units that are due for "recertification", loops through the batch comparing the ownerID - when a new owner ID is found, the next InvoiceID# is created, and the following recertification fees are placed on that invoice, untill a new, unique OwnerID is encountered. This is working very well with the single comparison.

I realized yesterday that I had a flaw in my data model for the script (not a first, I'm learning....) I need a new InvoiceID# whenever the script encounters either a new OwnerID, or a new AgentID.

I have Dwelling Units, identified by License#. Related tables with Owners and Agents. I set my script up assuming that an owner would always work with the same agent. Not true. An owner may have multiple agents (management companies) taking care of their properties. I send invoices to either the agent or owner, based upon owner's choice as to who they would like to handle their paperwork. This is handled by a "mail to" calculation field for addressing the invoices.

I sort all "due" certifications by Owner first then agent. I need my script to walk through each record, and create a new InvoiceID if it encounters a new owner, or a new agent.

My current script is attached for reference. Thanks in advance for any help!

Darren

Invoice Script.pdf

I am afraid I don't follow this part:

An owner may have multiple agents (management companies) taking care of their properties. I send invoices to either the agent or owner, based upon owner's choice as to who they would like to handle their paperwork. This is handled by a "mail to" calculation field for addressing the invoices.

Shouldn't this be handled by having an AgentID field in the DwellingUnits table?

I am afraid I don't follow this part:

Shouldn't this be handled by having an AgentID field in the DwellingUnits table?

Perhaps - if the fact that there is an agent listed triggered the mail to go to the agent - but not so. An owner can choose from a list of 5 areas of responsibility that the agent may be designated for - one of them is to receive this invoice. I use a calc to grab either the owner or agent name and mailing info based upon the checkbox on the list of responsibilities.

BTW - I didn't need a nested IF - I used a single IF statement with and "or" statement to trigger the new Invoice ID - sometimes simple works!

So are you all set now?

I'm good to go - the or statement seems to handle the situation perfectly!

Thanks again,

Darren