Auto create batch invoices?

[Darren Emery]

This one's going to take a bit to explain - I hope someone has gone down a similiar path, and is willing to tackle this one!

My DB tracks apartment rental licenses and inspections. Each month, I need to send an invoice to owners or managers who have units due for inspection the next month. I have a summary letter than prints just fine, sorted by owner, listing the units that are due.

The next step is to create an invoice, by owner, for every payment due. I don't want the inspectors to have to go through and create each invoice as the volume is pretty high (400 units each month, average of about 3 units per owner.)

I'm stumped on the relationship design to make this work.

The license table can create fees due. I need to batch these fees by owner (not by license number, every apartment gets a different license number) and assign the fee to a unique invoice. It seems like a two step child creation problem, with a summary field thrown in for good measure.

I'm not sure how to get all of the currently due fees to relate to a unique invoice grouped by owner.

Any ideas....?

[bcooney]

I can't really follow your data model. Could you post a simple ERD (not a pic of the relationship graph!)?

[comment]

It is always difficult to create invoices after line items (inspections in this case) have already been created. Here's a simple - albeit tedious - option:

Sort the inspections by owner. Loop through the records:

If $ownerID is different than the OwnerID of the current inspection, set $ownerID to the current owner's ID and create a new invoice for this owner. Set $invoiceID to the newly created invoice's ID, then return and set the inspection's InvoiceID to $invoiceID. Else set the inspection's InvoiceID to $invoiceID.

[Darren Emery]

I can't really follow your data model. Could you post a simple ERD (not a pic of the relationship graph!)?

Basic ERD attached.

As I drew this out, and thought about what I'm trying to do, my problem became clear (I think.) My goal is to view fees due grouped by owner name. I should have created another table, containing the owners. If each owner had a unique ID# - this would be a fairly simple issue.

Any thoughts on how to do this, without a new table? (12k records entered, I don't think I can convince the office staff to re-input this info!)

ERD.pdf

[bcooney]

You need an owner table, definitely.

Got some questions. Dwelling Units will be a child of Owner. Violations, to me, are children of Dwelling Units (or Inspections). What is the biz process of generating a Violation? How does that tie into Inspections? I don't see why you have the ViolationID in Code Sections. To me, you'd have a CodeID in Violations, and the Code table is what I call a "library" table.

What is the purpose of a Report table?

Wouldn't lnspection Fees be a child of Violations or of an Inspection entity? I'd put an OwnerID as a lookup in Inspection Fees to allow you to quickly gather Fees for an Owner (and show them in a portal).

hth,

Barbara

[Darren Emery]

You need an owner table, definitely.

Got some questions. Dwelling Units will be a child of Owner. Violations, to me, are children of Dwelling Units (or Inspections). What is the biz process of generating a Violation? How does that tie into Inspections? I don't see why you have the ViolationID in Code Sections. To me, you'd have a CodeID in Violations, and the Code table is what I call a "library" table.

What is the purpose of a Report table?

Wouldn't lnspection Fees be a child of Violations or of an Inspection entity? I'd put an OwnerID as a lookup in Inspection Fees to allow you to quickly gather Fees for an Owner (and show them in a portal).

hth,

Barbara

Good questions Barbara - thanks for diving in.

A violation is a specific item (event) that the inspector finds during a yearly inspection. After conducting the intial inspection, the inspector creates a report as a place holder for the associated violations, and the letters that will be sent to the owner. The exact same violation may happen more than once to a property, and the desire is to track how many times each type of violation occurs, so I broke these out by report, so I can tie the occurence to a specific report, by inspector and date.

The Code table is indeed a library, which will change every three years as we adopt new code editions. The same code number (ie 501.1.2) could be a different issue in three years, hence the library, with the ability to change the looked up code description.

Believe it or not - the inspection fee is NOT tied to the inspection itself - we do the inspection if paid, or not. And...there will be multiple inspections per report (initial, recheck, multiple rechecks, etc) - another reason I created the report table.

Now, to the true dilemma - can I create a sytem to go back through my 12,000 records, grab the owner name on identical records, and populate an Owner ID field? HELP! :o

[comment]

can I create a sytem to go back through my 12,000 records, grab the owner name on identical records, and populate an Owner ID field?

Yes, of course you can. One way to do this is practically the same as the one I outlined above for creating the invoices: you loop through your table (sorted by owner name) and every time the owner name changes you create a new record in the Owners table and grab its OwnerID.

Note that some manual inspection is required - e.g. you may have the same owner listed as "Joe Smith" in some records and "Joseph Smith" in others.

[bcooney]

"After conducting the intial inspection, the inspector creates a report as a place holder for the associated violations"

I'd simply have a parent Inspection record.

"how many times each type of violation". I'd create a ViolationType table.

If all you have is Owner Name, you'll need to create some sort of calc field that concatenates with Name to try to get a unique list of owners (perhaps the License#?). Then you can subsum by that field, and export. Open the export and sort by name and see if you have unique values. Then import into an Owner table. However, you don't have the OwnerID in any child tables. That'll also need to be done.

It just seems to me that your data model isn't the best that it can be. I don't see the need for a report table.

[Darren Emery]

Yes, of course you can. One way to do this is practically the same as the one I outlined above for creating the invoices: you loop through your table (sorted by owner name) and every time the owner name changes you create a new record in the Owners table and grab its OwnerID.

Note that some manual inspection is required - e.g. you may have the same owner listed as "Joe Smith" in some records and "Joseph Smith" in others.

I think I get this - check me on the logic if you would.

As the script loops through the table and the name is not identical to the previous name, it grabs that name, goes to the new table, creates a new entry of owner, grabs a new, unique serial of that ownerID, and returns that value to the intial table. It seems this leaves me with each license # with the first occurance of an owner name tied to the new parent, but what about all of the duplicates in the list? How do I get each and every license number tied to the appropriate owner in the new table?

It just seems to me that your data model isn't the best that it can be. I don't see the need for a report table.

That is very likely. I'm learning on the fly here - and under the gun to get it done!

[comment]

Yes, that's right. If the name has changed - you create a new owner record, set $ownerID to the OwnerID of the newly created record and return it to the license record. If it hasn't changed, then you simply use the existing $ownerID to populate the OwnerID field in Licenses.

There are faster methods, but they are more difficult to explain - and you only need to this once.

[Darren Emery]

Yes, that's right. If the name has changed - you create a new owner record, set $ownerID to the OwnerID of the newly created record and return it to the license record. If it hasn't changed, then you simply use the existing $ownerID to populate the OwnerID field in Licenses.

Ok - cool - I think I'm on to a solution here. The other hurdle I see - on every license record I have the owners name, their contact info (yeah - unnecessary duplication from the get go) the agent's name, their contact, etc. For some properties, the owner is the main contact, for others, the agent. I send letters to the appropriate contact based upon a checked field on License.

Do I export all of this info into the new table, or just stick with owner names, so I can properly group the data?

This thread is pointing out my weakness in relational theory and design. I'm duly humbled...

[Darren Emery]

Ok - cool - I think I'm on to a solution here.

Perhaps - rather than the new table, and all that entails:

Can I create filterd portal, using another table occurence, that relates based upon the invoice date (this will pull all records that were invoiced in a given month - they all batch on the same day) and relates on a global field "owner name?" Would that allow my staff to pull up a list of the invoiced items, on the date of the letter, atributed to that owner... (this is my goal - I have a sub summary letter that is working for sending out inspection due letters, sorted and summed by owner name. I need a way for our staff to pull up all the invoiced items by owner, when they come in to pay, or when they send in a check - sometimes 30, 40, 60 records - that's a lot to look up by license #)

BTW - thanks indeed to comment, barbara, bcooney, et al. So much appreciated!!!

[bcooney]

Where do you store payments? Do they span invoices?

Your portal idea sounds OK, as a stop gap until you rework the data model.

[Darren Emery]

Where do you store payments? Do they span invoices?

Your portal idea sounds OK, as a stop gap until you rework the data model.

Another good question - and I don't have an answer yet, as I'm still trying to solve these issues! I've bought myself some time with the end users, so it looks like it's time to rework the model. I've never done an invoicing solution, so I'm on a steep learning curve. I've been watching John Mark Osborne's video on Filemaker 10 intermediate, and that has helped a great deal. However, I do not have any "inventory" - just fees - so I'm not sure how to structure that data model.

Now - to figure out how to pull all of this data out and populate my new tables...

[bcooney]

How about you play the customer and describe the process? I'm willing to take a stab at the correct model.

[Darren Emery]

How about you play the customer and describe the process? I'm willing to take a stab at the correct model.

Sweet - thank you!

So, I'm a real estate investor, and I own 97 residential units. I own a few 12 unit buildings, a few homes, and the rest duplexes. Because I have so many properties the code office has agreed to stagger my inspections over the next three years, so I don’t get hit with the $65 per unit inspection fee all in the same year.

I get a letter at the beginning of February that tells me that 30 units are due for inspection. I have 30 days to call and schedule the units for inspection (not to complete the inspection, just get them on the books), and I have 30 days to pay the $65 per unit. My letter lists each dwelling unit by address, apt# and license number. I'd like an invoice with this letter to help me remit payment (I haven't decided if I'm going to mail in my check, or pay online through Gov-pay) showing a detail of the units and the fees, but they haven't provided this. (I hear this is in the works...)

I call and schedule the inspections – and I mail in a check for $1,950. I sure hope the office staff gets each unit properly credited for these payments, as I know that each dwelling unit will not be considered in compliance until it passes the inspection, and the fee is collected. Right now, I hear the office secretary is tracking these payments on an excel spread sheet cuz the Filemaker guy is “working on it.” Doesn’t inspire a whole lot of confidence…you know what I mean?

If a dwelling unit passes with flying colors, it won’t have to be inspected again for 5 years. If there are violations, but I get them corrected within the time frame (usually 30 days) – the unit will be put on a 3 year rotation. If I fail to get the items corrected, or if I don’t pay my fees on time, the unit gets put on a yearly rotation.

The way I understand it – my fees aren’t considered due until the code office mails out my letter, letting me know it’s my turn for inspections. Then the clock starts ticking.

And BTW – did I mention that this program was recently forced upon us landlords by the city council…and I’m not really happy ‘bout that?

[Darren Emery]

I just realized - my office staff is my other customer. Let's take a look at it from their direction.

So - I have to take in all this money, track it, and enter it (when he gets the system up and running.) I'd really like a way to look at an invoice - something that shows me every currently due fee, grouped by owner name, with an easy way to indicate that the payment was received, so the inspectors know they can close each particular file.

Right now a letter is printed by a sub-summary report, sorted by owner name, and that works fine for the client, but I can't get to that data unless I reprint the report. This is cumbersome, and then I can't really enter anything from that report. I often have a list of 10, 20, or many more payments that need to be noted on individual dwelling units, but I don't have a way to pull up which ones this guy standing in front of me needs to pay for, unless I pull up everything he owns. I need to know just which ones were included in the letter that went out 15 days ago.

An invoice, viewable on screen, by owner name, with currently due fees would be great. If only Darren had built this database correctly from the beginning....

[comment]

The other hurdle I see - on every license record I have the owners name, their contact info (yeah - unnecessary duplication from the get go) the agent's name, their contact, etc. For some properties, the owner is the main contact, for others, the agent. I send letters to the appropriate contact based upon a checked field on License.

Do I export all of this info into the new table, or just stick with owner names, so I can properly group the data?

It's hard for me to answer this in detail. I don't see your data - and in my experience in these forums, there's a trapdoor behind every "etc.".

In general, all details that pertain to the owner (and thus do not change from property to property) go into the Owners table. Details that are specific to the property stay in the Properties table.

But it can get more complex, e.g. with the agents you probably need to repeat the process, populating another foreign key field with the agent's ContactID (assuming owners and agents go into the same Contacts table).

[Darren Emery]

It's hard for me to answer this in detail. I don't see your data - and in my experience in these forums, there's a trapdoor behind every "etc.".

In general, all details that pertain to the owner (and thus do not change from property to property) go into the Owners table. Details that are specific to the property stay in the Properties table.

But it can get more complex, e.g. with the agents you probably need to repeat the process, populating another foreign key field with the agent's ContactID (assuming owners and agents go into the same Contacts table).

Thanks comment - I sure understand the challenge of providing guidance in this format!

Question: can I pull all of the owner's data (name, address, city, zip, phone, email...etc) out with one script, or should I create an individual script to pull each piece of information? Seems to me that this should all be done at once, with new records triggered off of each unique owner name...and then that record with the first occurence of a unique name supplying all the pertinent info?

Am I looking at this correctly? (this kind of script is a stretch for me, but I have cloned DBs I can experiment on without corrupting the real data)

[comment]

can I pull all of the owner's data (name, address, city, zip, phone, email...etc) out with one script

Perhaps it's time to move to plan B: export the owner fields, after sorting them by name (or phone or e-mail - whichever you think is the best unique identifier). Select 'Group by [the unique field]' when exporting. Import into a new table (Owners).

Next, define a relationship between the two tables, matching on the unique field, and use it to populate the OwnerID field in Properties with the value of the related OwnerID in Owners. Finally, switch the relationship to use OwnerID as the matchfield on both sides.

[Darren Emery]

Perhaps it's time to move to plan B: export the owner fields, after sorting them by name (or phone or e-mail - whichever you think is the best unique identifier). Select 'Group by [the unique field]' when exporting. Import into a new table (Owners).

Next, define a relationship between the two tables, matching on the unique field, and use it to populate the OwnerID field in Properties with the value of the related OwnerID in Owners. Finally, switch the relationship to use OwnerID as the matchfield on both sides.

Now that seems very straight forward. I'll give this a try - thanks!

[bcooney]

I'll have some time, I think, this weekend to propose the start of a model. However, it seems to me that you are inclined to reach for a script to duplicate data rather than build a relationship. Also, don't be afraid to add tables (tables that serve simply for value lists, like Code table).

Meanwhile, get your Owner's table up and running. Everything should relate ultimately with IDs.

[Darren Emery]

Thanks bcooney - I really appreciate the help!

Darren

[bcooney]

Played around a bit. Take a look, then we'll discuss. I don't have a payments table, which is probably a good idea. There is another thread that is currently progressing, debating the allocation of payments to invoices. You may have the same dilemma. Must payments be allocated to Inspections or can payments simply be a child table to People?

Darren.zip

[Darren Emery]

Played around a bit. Take a look, then we'll discuss. I don't have a payments table, which is probably a good idea. There is another thread that is currently progressing, debating the allocation of payments to invoices. You may have the same dilemma. Must payments be allocated to Inspections or can payments simply be a child table to People?

Wow - there's a lot to look at here - and a lot for me to learn. Thanks for taking the time to coach on this one.

I have been following the other thread - will go through that in depth again.

Regarding the payments: they do NOT have to be allocated to inspections. As long as the inspectors can view the payments (date and amount) they can determine if the timing matches the inspection they are working on. These payments will typically be spaced about 3 years apart (at a minimum, 1 year) so it should be pretty easy to determine a date based relationship on the fly. The inspector will not "close" the current report (inspection documentation) untill they see that the payment has posted.

[comment]

The inspector will not "close" the current report (inspection documentation) untill they see that the payment has posted.

I would read that as "yes, the payments DO have to be allocated to inspections".

[Darren Emery]

I would read that as "yes, the payments DO have to be allocated to inspections".

I viewed this in a structural manner - as in the system does not have to allocate(relate) the payment to the inspection. The inspector will informally do this by simply closing the report.

When the payment is posted, the license# should reflect this event, but not the inspection.

[bcooney]

I viewed this in a structural manner - as in the system does not have to allocate(relate) the payment to the inspection. The inspector will informally do this by simply closing the report.

Report? Sorry to nitpick, but what do you mean by "report?" Do you mean the Inspection record? If that's the case, then I agree with Comment that you should have Payments as children of Inspections. Who "closes" the Inspection is a matter of biz work flow. It may be the person entering the payment, or the priv set assigned to the Inspectors.

When the payment is posted, the license# should reflect this event, but not the inspection.

The license#?

[Darren Emery]

Report? Sorry to nitpick, but what do you mean by "report?" Do you mean the Inspection record? If that's the case, then I agree with Comment that you should have Payments as children of Inspections. Who "closes" the Inspection is a matter of biz work flow. It may be the person entering the payment, or the priv set assigned to the Inspectors.

The license#?

I sure don't mind the nitpick - it helps me work through the details. The report is what the inspectors create when they return to the office after conducting the first inspection on a particular apartment. The report is somewhat of a join table - relating the many inspections, violations, and letters to this once a year process of recertification. I broke this out into a report, rather than just an insepction as a child of the apartment because it may take 2, 3 or more inspections to obtain this year's recertification. I wanted a way to group all of these items into a coherent group - thus a report. (this report is not a written docuement - just a placeholder - perhaps report is a poorly chosen term.)

Re: licesne # - each and every dwelling unit has a unique license #. this is the main key I use to tie all the data together.

[bcooney]

imho, your use of "report" is not going to serve you well.

The requirement for multiple inspections leading to a Certification is new to our discussion, isn't it?

Perhaps, Inspections have children Visits, and Units have children Certifications?

I don't pay for each Visit, correct?

btw, there are no passwords on the file. I created it in FM11. Do you really have FM9 only?

[comment]

It does sound like "reports" are "Certifications". And if - from the financial POW - a Certification is issued only when the total amount paid matches the number of child Inspections, then indeed Payments can be a child of Certifications.

However, this raises the question whether Violations should be a child of Certifications (carried forward from inspection to inspection) or of Inspections (i.e. each inspection starts from fresh).

[Darren Emery]

To answer a number of questions:

Yes - vs. 9.

The certification is approved only after all violations are corrected (often, after a number of reinspections) and the intial fee (a set 65, not per inspection) is paid.

And I thought I mentioned multiple inspections per unit each year, but I may not have...

[bcooney]

So, you pay for a certification, not an inspection. Inspections happen along the way to Certification approval. Also, violations may be created (as children of Certifications?, Units? or Inspections?). They all must be closed in order to approve the Certification.

[Darren Emery]

So, you pay for a certification, not an inspection. Inspections happen along the way to Certification approval. Also, violations may be created (as children of Certifications?, Units? or Inspections?). They all must be closed in order to approve the Certification.

You got it.

I have violations as children of certification. The recertification process will occur 1/3/5 years hence, based upon number of violations and time for repair.

The violations must be closed, and payment received, to approve the cert. To complicate matters - if the payment is not received within 30 days of invoice, the payment doubles.

[bcooney]

How you relate violations is dictated by the biz process. I certainly see that a violation needs to know its Unit, perhaps the InspectorID, and perhaps the InspectionID at which it was cited (knowing the InspectionID gets you all the other IDs).

Overall, don't be afraid of adding tables to explicitly capture each step in the process, and be careful of your terms. For example, you initially stated that "units are due for inspection." Actually, they are not. They are due for re-certification.