One-to-many ... maybe

[LaRetta]

Our customers are stores (wholesale) and individuals (retail). The retail customers pay their own invoices (usually via credit card). Some stores pay their own invoices; others are chain stores. With Chains, the invoices are sent to the store but payment comes in a group (sent as one check with a breakdown) from the headquarters. They take cross-credits between stores, etc. and these accounts intermingle quite a bit. I've established a PayerID field in Customers which holds the CustomerID of the corporate headquarters (payer) from a different record in customers. A calculation, if the PayerID is empty, uses the CustomerID as the 'official' payer.

Management wants the chain accounts to instead produce a Chain statement (and the chains have requested this as well) which would group all invoices being paid by each payer. They also want these invoices viewed by Customer (per store). So if chain Major Foods pays for 250 customers (we have this), the 'Major Foods' account would list each invoice. The next record may be a small individual store or a retail customer.

Another table was needed (I thought) - a Payers table. But, for Management to view records in the Payers table, even the small stores must be included (one-to-one). I established the Payers table and wrote the CustomerIDs into a multiline field for each Payer. I'm unsure of the proper perspective and approach. If the Payer has the CustomerID, then EVERY time we add a customer, we will also need to establish a Payer record. Of 120,000 customers, only 24,000 are members of a chain.

Can you see my confusion? Yep, I'm tired but even if I wasn't, the proper attack would be unclear to me. I need to provide the Payer as a record and a portal of ALL invoices from ALL their stores. Can you help me here? I hope I've been clear enough ...

LaRetta :wink2:

Edited January 6, 2006 by Guest

[comment]

Hmmm... not sure I understand this completely, but take a look at the attached.

Chain__sInvoices.fp7.zip

[LaRetta]

Hello Michael!

You nailed it! I've spent hours researching here for ideas but couldn't get away from the need to add a Payers table - and I don't need one! Creating another table doesn't ALWAYS make the most sense and this is a prime example; multiline in Customers does the trick! And my small stores and retail customers don't even NEED to be assigned a Payer!

I can't thank you enough, sir! You've saved me ... again.

LaRetta

[LaRetta]

Hi Michael,

I was beginning implementation of your process and I've hit a snag. I know I could solve the problem but I would really love to see how YOU would make the following changes to your demo. I assume I'll need to add globals to be included in the match.

When viewing the Invoices2 portal from each Customer record, I want User filter abilities as follows:

1) From and Thru date range (based upon ShipDate)

2) Only invoices with amounts not equal to 0 (invoices Balance is static number field)

3) Only invoices past 60, 90 or 120+ days - radio for each? Invoices has a DateDue (static date field)

4) Only invoices with 1) Balance = Invoice Total or 2) Balance < Invoice Total but not 0 (Invoices static field called TotalInvoice, number)

5) DateLastContacted (static date in Invoices when we last faxed or called them on their past due)

I would prefer these options be and/or (so maybe need an -ALL- option for each category?). As I began to put this together, I felt convolution rear its ugly head. This process is critical to us. Can you help me a bit more here? I'd be forever grateful (as if I'm not now, smile).

LaRetta :wink2:

[comment]

That's a tall order... why don't you post something, so I don't have to start from scratch? I'm a bit busy now, but I'll try and take a look. My first impression is that it won't be pretty.

[LaRetta]

Hi Michael,

Yeah, not pretty at all. How can I use a field in a join if the associated field in Invoices2 is empty? This is one frustrating issue with multi joins - if only ONE of the related fields is empty, the whole things breaks). Well, for ShipDate it was no problem because I had a ScheduledShipDate field and already had a calculation (date) called cInvoiceDate so I used that instead of ShipDate. I needed that for multiline join on dynamic reporting (crosstabs). But many of the invoices haven't received a NoticeDate yet so it was still breaking. I decided to forget that part. If you have a suggestion here that'd be wonderful but I'm unsure if it's THAT important. After all, If a customer has THAT MANY unpaid invoices, we'd be on them like flint - list or no list.

I understand you're busy and I'll begin implementation with what I have. Any suggestions or improvements would be appreciated. Thank you!

UPDATE: We never have credit invoices (invoices with minus amounts). An invoice is paid in full and any overpayment creates a floating credit on the account, which is applied to their next invoice. So that's why I haven't accounted for minus dollars.

LaRetta

Chain__sInvoicesREV.zip