2 Questions, pls help if you can

[marc]

Hi all, I have 2 questions:

 

1) I want to have 3 security levels, one for admin, one for manager and one

for employees, the only thing I will allow the employees to do is browse

records, and push 2 buttons, but When I set it in security it doesn't work.

 

2) I want to create an invoice, My database tracks inventory, and records

are kept by UPC number. I want to make a table that the employee entrees a

list of UPC's and quantity sold and the systems pulls up the info from the

records and subtracts the quantity from the QTY on Hand field.

 

I would like to do this on the same database, (this way the employee doesn't

have to open a new file)

 

Please send any info or suggestions. I need to make this database as easy to

operate as possible.

 

marc.

[David Head]

...when I set it in security it doesn't work

[marc]

Under groups, The group Employee has read only set for all the fields, yet they can modify any field.

 

"I want to make a table that the employee enters a list of UPC's and quantity sold and the systems pulls up the info from the records and subtracts the quantity from the QTY on Hand field."

 

I think you need to review your database structure here. It is important to define what you want to do and then get the file structure right to store the data.

 

I don't quite understand what you are getting at. Basically this system is to track inventory of audio CDs. I want to make a layout that when the employee goes to it he/she lists all the CDs they are selling on one page (layout) and the system calculates the price and then substracts the quantities from inventory. Usually customers purchase more than 1 cd at a time.

 

I guess I could do it on 2 different databases and just have the inventory database call the other one then close it when complete. I was just thinking of loading time.

 

When I said table I meant what the layout would look like.

 

I hope this makes my question a bit more clear.

 

Marc

[David Head]

Under groups, The group Employee has read only set for all the fields, yet they can modify any field.

[marc]

As for the invoice bit of my question, here is what I have:

3 files, Inventory, Invoice and Line File

the inventory file has a UPC#, title, price, QTY on hand and qty sold to date. These fields are the ones that that need to be passed to the invoice file and the invoice file must update the qty on hand and qty sold to date on the inventory file.

Idealy the user would enter the UPCs (manually) on the invoice file, it would pull up the info from the inventory screen, then when a script is ran on the invoice it would update the qty on hand. the invoice has a field called qty sold which would always default to 1. The math would look like so: QTY on hand= Qty on hand - qty sold, and would update the inventory, also QTY sold to date= qty sold to date + qty sold.

The price would be calculated on the invoice and is not needed to pass back to inventory. (total= price * qty sold) sub total = sum of total.

 

The line file from what I understand would have the following fields: UPC, Title, Qty on hand, qty sold to date, Price.

how do I setup the relationships? And how would I configure the portal on the invoice file?

 

Hope all this makes sence.

 

Thanks in advance for your help.

Marc.

[David Head]

It's much easier to help when you explain in detail what you have and what you want!

 

These are the minimum fields you should have in each file:

 

INVOICE

Invoice Number - text or number field, probably an auto-entered serial

Date - date field

Invoice Total - calculation (number) - =Sum(Line::Line Total)

 

LINE

Invoice Number - text or number field

UPC - text or number field

Title - text field, lookup from INVENTORY based on relationship

Price - number field, lookup from INVENTORY based on relationship

Qty - number field, default to 1

Line Total - calculation (number) = Price * Qty

 

INVENTORY

UPC - text or number field, set to be strictly unique

Title - text field

Price - number field

Qty on hand - number field

Qty sold to date - number field

 

There will be a number of relationships between the files:

 

From the INVOICE file,

to Line = Invoice Number :: Invoice Number

From the LINE file,

to Invoice = Invoice Number :: Invoice Number

to Inventory = UPC :: UPC

From the INVENTORY file,

to Line = UPC :: UPC

 

In the INVOICE file, you set up a data entry layout for the invoice with a Line portal on it showing the UPC, Title, Qty, Price and Line Total fields.

 

The tricky part about all this is the inventory levelling but you must get the structure right first. From your description, you had the wrong fields in the wrong files.

 

For now, get the above structure right and get back to me about the inventory levelling scripts when committing the invoices.

[marc]

Isn't it amazing, If I explain clearly, someone can reply clearly

 

Ok this is done. I think I sortof understand the comcept, but I will let you continue, I'll ask a few questions, the line field, is is used for every line in the portal? if so doesn't that mean the line file will have an entry for every item sold?

So if it works the way I think it will then the line total is entered on a line per line basis just like the UPC will be. Then I assume I would do a SUM for line total on the invoice to give me the sub total.

One more note, I want to bring over the current qty on hand on the invoice, (but I won't print that field) so I would add QTY on hand to the invoice and line field and treat it the same way as I have for price, upc, title.

 

Waiting for the next step of the setup, which is probably how to adjust the qty on hand and qty sold on the inventory database, depending on the qty sold on the invoice.

 

Marc.

[marc]

another quick question, how do I make is so I click on the UPC field and that I can type an entry?

[norm douglas]

Is this the Marc I think it is?

[marc]

I don't know, which marc do you think I am?

I am from canada.

[marc]

Dave, I have another question,

 

I have another requirement for the invoice, I would like to be able to enter items which inventory is not tracked. For instance, if someone purchases a bunch of CDs I would enter the UPC and the portal would do it's thing, but if my customer purchases a CD player as well I would like to be able to enter it as well, but I have no need to track the inventory. So all I would do is type the info manually, How would this be done? Can it be done in the same portal?

 

Marc.

[marc]

I was talking to my friend and he mentioned something interesting, which will change the requirement of the invoice. The thing is that I no longer have a requirement for an invoice. Instead I would like 2 things.

1) a way of seraching multiple UPCs at once on the same page.

 

2) change the invoice system to a batch update system, Meaning, at the end of the day I enter all UPC's and I enter in a field called "adjustment" - or + the quantity.

Example, I sold 10 different cds in a day, at the end of the day I go in the adjustment screen and enter all the UPCs and put -1 next to each, then it will update the inventory database when I click the submit button.

 

I think this makes the system I am designing a little easier. I think though with this case I would need 4 files. Inventory, multiple search, adjustment and line.

Could you please help?

 

Thanks

Marc.

[David Head]

With respect, this is getting very frustrating. If you were a client I would have sacked you by now for changing your mind so much or else you would be looking at a large invoice. It seems to me that you have not really done the groundwork properly to find out what you or your friend really need.

 

Email me off this forum with the requirements and I will provide some answers - developer@prioritye.com.au