Portals & Line Item File

[winesellr]

Hi All-I'm New and have a problem.

I'm writng a cash register program for my business.

I have a line item table and a cash register table with a portal for the entry of the products sold.

The portal makes the individual files in the line item file just fine-including all the lookups to the inventory file(size, price etc...).

My problem is how does the line item file accurately receive and attach and keep track of for printing all the other universal data about the sale in the cash reg table like sales#, cashier# terminal # and especially the Sales Tax, Shipping Fees, ShipTo Customer Data.

In other words-all the data that applies to the total invoice not just the individual line items. And those fields that are calculations based on the subtotal and totals on the portal screen but not directly on the portal itself.

[ESpringer]

You can get the records in the line items file to *display* or *print* data from the associated main record without making it *store* all those things. Just drop the relevant fields right onto the layout you're using to print the line item records. (There's a drop-down list at the top when you specify a field, so that you can choose to display info from a related record. In this case, there's just one related record for each line item, but if there were more than one, FM would choose the first one either by relation's sort order or by creation order.)

If you need to represent the data directly in the line item record, for some reason, use an auto-enter field (to keep the data "static") or a calc field based on the relation. In FM7 the relationship is already in place because you're using it in the other direction to create line items.

[winesellr]

Thanks for the reply ESpringer. I appreciate it.

Unfortunately, the first method you suggested doesn't work for me because the data needs to be kept in or attached to the line file permantely for later accountability (Who sold the goods-when- what terminal etc..). And to be able to run summaries to account for the cash.

As soon as the sale is made the portal is deleted and a new one is created. There will be up to 3-4 people simutaneously doing sales (checkout counter at the store).

Obviously, with this method and the data disappears after every sale.

I don't understand what the auto-enter method is.

The other suggestion works better-meaning the calc field.

Let me detail the flow of data.

The data is originally auto-entered in fields outside the portal on the layout of cash register.

In the portal we enter the sku# and the items lookup from the Inventory file.

All the unchangable numbers like sale#, cashier#, terminal# flow via lookups to the line file and are in each line item file.

Good so far.

Now, the data that applies to the sale as a whole-like the sales tax rate and calculation total and the shipping costs and the subtotal and the total-how do I place these on the individual line items files when the numbers are really based on the whole transaction. Not individual items.

Am I on the wrong track.

[bruceR]

Your conclusion is incorrect. You need to set up a proper table structure with invoice and line items tables. You should NOT be deleting the invoices (parent records).

[ESpringer]

When you speak of "deleting the portal" I wonder what you really mean! As Bruce says, you should not delete the main records for which the line items represent the details. Such a record is usually called the "invoice", and what you called the "cash register" file should probably be called "Invoices" or "Transactions". (The best name for a file is whatever each record represents.) Or if you do want a cash register "user interface" file that doesn't have any live connection to already-completed transactions, then you might have a script that takes the global and/or summary fields from the cash register file (whatever it is that currently allows the cash register person to announce the total due, etc.) and write these to a new Invoices record. Make sure to give the invoie a serial number, and write that serial number to each line item as the "key" to keep the records connected.

The reason you wouldn't delete invoice information is precisely because of what you're noticing: you need a place to store the info that's proper to the *whole* transaction: when, who, total amt, how and when paid, shipping and tax totals, etc. It doesn't make sense to attach it to each line item, because (as you notice) some of it is logically not attributable to any specific line item(s).

I sense that you're somehow not comfortable with having your archives use a related table structure. Why? Whatever your issue is, it probably has a straightforward answer...

[winesellr]

BruceR said:

Your conclusion is incorrect. You need to set up a proper table structure with invoice and line items tables. You should NOT be deleting the invoices (parent records).

Thanks BruceR

I'm definitely on the wrong track here. A re-assessment is in order.

Pat

[winesellr]

ESpringer said:

When you speak of "deleting the portal" I wonder what you really mean!

As Bruce says, you should not delete the main records for which the line items represent the details. Such a record is usually called the "invoice", and what you called the "cash register" file should probably be called "Invoices" or "Transactions". (The best name for a file is whatever each record represents.)

Or if you do want a cash register "user interface" file that doesn't have any live connection to already-completed transactions, then you might have a script that takes the global and/or summary fields from the cash register file (whatever it is that currently allows the cash register person to announce the total due, etc.) and write these to a new Invoices record. Make sure to give the invoie a serial number, and write that serial number to each line item as the "key" to keep the records connected.

The reason you wouldn't delete invoice information is precisely because of what you're noticing: you need a place to store the info that's proper to the *whole* transaction: when, who, total amt, how and when paid, shipping and tax totals, etc. It doesn't make sense to attach it to each line item, because (as you notice) some of it is logically not attributable to any specific line item(s).

I sense that you're somehow not comfortable with having your archives use a related table structure. Why? Whatever your issue is, it probably has a straightforward answer...

I'll address your points-

>I mean I actually deleted the main invoice (mistakenly)-all the line items and sale info outside the portal. I thought capturing the line items in the archive was enough.

>This file is actually called Sales currently. I thought that since many people would use it simultaneously it should not have any data in this file just a gui of a cash reg. Therefore speeding up the transactions at the pos.

>I am stuck in retail sales-think re:the mechanics of making the sales.

>Is the structure Cash Reg(Sales) --->InvoiceHistory---->Line ItemArchive

or just

InvoiceHistory---->Line ItemArchive

where I just keep adding invoices to the same file where they are created.

Which is faster?

>I have no particular bias- just thought the line items were the most important data to keep for inventory tracking purposes ( i have a portal back at the Inventory file to show past sales and to delete sales from invtotal).

I'll glady use a related table structure-I'm just a little unsure of my options at the moment.

I Thank You ESpringer for your time to point me on the right path.

Pat

[ESpringer]

From this point on, I'm surely not the best person to help with details: if you have a multi-user sales environment, there are surely efficiency and security considerations beyond table and relational structure. (I don't do anything like retail applications; others here do.)

I will point out one thing, though: if you make a GUI table separate from the transactions and line items tables (perhaps in another file), there might be certain slight advantages: you could back up your *data* file (tables for invoices and for line items) regularly and it would be a bit more storage-efficient not to be re-archiving daily all the scripts & user interface details of the data entry layouts. Users of a cash-register UI file could also be somewhat more easily isolated against tampering or inadvertently messing with portions of the data you don't want altered.

I'd recommend browsing and doing searches through the forums here, with an eye for sales or invoices databases, or post more detailed question in more specific forums (Accounts & Privileges, Relationships, Define Fields)... Good luck!