UGH!!! Not sure where this fits

[Vaughan]

Try *really* hard to avoid a system that relies on exports and imports. What's wrong with having a field in the database that gets marked "posted" but leaves it in the same file?

How is the rest of the system set up: using related records?

[amypaulsmom]

After the invoices are posted - they are delated from the original database. The first database is basically - a dummy database and then second holds all the information. The related records (item information) is pulled from a line items database.

[danjacoby]

Simplify.

Get rid of the "dummy" database; there's no need for it.

Simplify tracking payment, etc., through extra fields in the main database. This will also simplify the find; just do a two-request find, one to find records that match the basic criteria, and a second request (in the same find) to omit the ones that are already paid.

Doublecheck your calculations -- without specifics we couldn't tell you why there appears (I repeat, appears) to be an extra "0" in the summary, but I'd bet it's because the calc is screwy.

I like simple solutions, I do.

[amypaulsmom]

I have the "posted" database - so that they are not making payments on the same database and adding other items at a later date. The "posted" database is a look only - when it comes to the invoice entry screen.

From other POS systems - this is how I have seen it work. Each entry has a separate invoice and an invoice can have multiple line items.

That is my reasoning for the "dummy" and "posted" databases. It makes sense in my head.

It's still that Find function for the Statement that I am working on. I can see where you are going - I will have to work on that. Need to work on interest payments the afternoon.

Thanks -

any other helpful hints are appreciated.

[Anatoli]

quote:

---

Originally posted by amysmom2000:

From other POS systems - this is how I have seen it work. Each entry has a separate invoice and an invoice can have multiple line items.

That is my reasoning for the "dummy" and "posted" databases. It makes sense in my head.

Thanks -

any other helpful hints are appreciated.

---

You set yourself to work too fast without proper relational model.

The dummy and posted db are for nothing.

You need couple of databases:

Customer

Invoices

Invoice lines in relation to Invoices

Possibly Products

Maybe Transactions

Maybe Contacts details

Payments

and some housekeeping files.

Almost anything can be built on those databases.

[Fitch]

This is fun... you have definitely strayed from the POLR, amysmom.

Anyway... the extra 0 can only be explained by a funky calculation. Give us some details, please.

As for omitting the invoices, if I've understood you correctly, one way to do it would be to set up a self-relationship, let's call it InvoiceMatch -- invoiceNumber::payingNumber.

Now make a field, ZeroThing that adds them up (or does it subtract them?). Sum(InvoiceMatch::amount)

Now find your invoices and make a script that loops through them and omits them if ZeroThing = 0.

I have to agree with the other folks, though -- this sounds like way more trouble than is necessary. But hey, they laughed at Edison!

[amypaulsmom]

I am working on a statement screen for an invoicing database. When an invoice is posted - it is exported to a new file. When a payment invoice is posted it is exported to the same file with a new invoice number and also the paying invoice number. The statment that I am creating is held within the file that all the invoices are posted to.

When I go to the statements - I want it to lookup both the invoice # and the paying invoice # - if they are the same to sum the found set. If the sum is =0 then I don't want the invoices to show on the statment.

I can do my find manually and it finds the found set - but then I created a summary field that totals the balance on my found set. This field adds a 0 to my calculation (ie. if it is $500, it shows $5000). That's one problem. My second problem - is after I do the find - how do I get it to omit that record from the statement layout?

I hope this makes sense - I have had wonderful advice from this forum in the past.

[amypaulsmom]

Okay - I am appreciating all your advice, but I guess I am getting a little frustrated as well.

The reason I have the additional "posted" database - is basically so that they can not come back and add things to the original invoice. If I can do this in the "invoice entry" database - that would be great.

Suggestions??

I do thank you for all your advice and support.

[amypaulsmom]

Regarding the Omitting Invoices -

This works - except when I create my script and do the omit record step the Zerothing field changes and then the amounts are different. So the next time through the loop - is doesn't catch the next needed records. I can omit multiple records - but I don't know how many I am omitting each time through the loop.

Does that make sense

[Fitch]

Good point. So let's make ZeroThing just a number field, and the loop can go through and mark it with a "1" if Sum(InvoiceMatch::amount) = 0

... or you could use a text field and mark it with an "x" it doesn't matter...

... or you could use Replace() to mark them (is this thing multi-user?)...

Then Omit the marked records from your Find.