Need Help with Setting up Fields, Layout

[fmakerNewbie]

All,

I am a newbie at this so it might be really stupid question.

I have a layout setup that lists out a form.

column 1 QTY(quantity)

column 2 Description (Drop down menu)

column 3 Price per unit

column 4 Price (Price per unit x QTY)

- Currently I have to enter Quantity(which is correct)

- Then I select an Item from the Description Column (these are listed values)

- At this point when an Item is selected I would like the "Price per Unit" to automatically enter but I don't know how (currently i need to manually enter the price)

- Then the Price total does a calculation of QTY x Price per Unit

I don't know how to get values for description to reference their prices.

Can someone please help me.

Thanks

Chris

[ernst]

Hi Chris,

One often used method to achieve what you want is to have a separate 'articles' file with fields for description, price, supplier, etc.

In the 'line items' file that you already have, you then define a relation between the description field and the description field in 'articles'

Then you define your price field to have an autoentered looked up value from the 'price' field from the 'articles' relation.

Now once you enter a description that exists in your article file, the price entered in 'articles' will automatically be entered in your 'line-items' file.

The above is a very basic explanation on how to do this, numerous examples exist both here on the forum in the 'Samples' section, and elsewhere on the net.

Regards,

Ernst.

[fmakerNewbie]

Thanks

From Other Database experience I figured I would need to have another relational database.

So what you say sounds correct.

Another Database that has

Item Price

Foo 1.00

Bar 3.00

Food 5.00

Problem I am having now is this.

I selected create new blank file in FM6. When I go to enter the Fields

(ie. Name and Price)

it shows up 2 text boxes

but I want a list like above that I can add new items to

Is this possible?

I'm sure it is but im not sure how..

I can do this in Excel but is there a way to migrate the data into here?(Import)

I don't have the data in Excel so setting it up correctly instead of importing would probably be better

Thanks again

[ernst]

Hey Chris,

Yes you can show the fields as a list. You'll have to go into layout mode, choose 'new layout/report' from the 'layout' menu and then select 'Columnar List/Report' from the presets. Filemaker will the guide you through the steps to ceate a new layout.

And yes, there is an 'import file' feature. Checkout the 'File' menu!

And maybe consult your manual???

Regards,

Ernst.

[fmakerNewbie]

Hey sorry I am doing this for someone that doesn't know how to use computers.. they just want a gui to this, bought this software because someone recommended it, and now asked me to help them set this up.

One thing I noticed.

- I created the new layout.

- The 2 Fields ("Define Fields" - Description, Price) are the column headers.

But now how do I add Items?

I have seen Layouts like this but with a Green "+" to add a new entry

Did I miss that in the Layout Wizard?

[ernst]

>>I have seen Layouts like this but with a Green "+" to add a new entry

>> Did I miss that in the Layout Wizard?

Well I don't think the layout wizard is that helpful!

View your new layout in 'browse' mode and add records by choosing 'New record' from the 'Record'menu.

And I still think that it is a good idea to have a look at a manul, also if you do this for somebody else.

Ernst.

[fmakerNewbie]

thanks with a little investigating i created buttons that i inserted into the header (actually before i got this response from you) (go me )

Only thing left is the original question which is create a relationship between the First File (Form Layout) selecting Item (which gets items from new file Items Inventory) and then have the Price per unit column reference the price of that item and insert it.

I know this is a little much.. sorry if its alot...

[fmakerNewbie]

Little Problem with getting Relationships to work properly.

I created an Inventory DB as a seperate File.

This file has:

Dept Size Item Price

When I try to setup a relationship for my Order Form page it doesn't seem to work properly.

Here is what I have so far:

On my order form I need to have Product Unit Price match WHERE Form::Size=Inventory::Size and WHERE Form::Description=Inventory::Size THEN Form::Price=Inventory::Price

This didn't seem to work properly so I did the following just to double check that I was correct.

I have Product Unit Price Defined as a Calculation:

If( IsEmpty(Quantity), 0, 1) * Inventory::Price

I know the above will select the first seen Price and not match the size but this was just to test that I was doing this correct.

For some reason this only displays on my first Item though.. doesn't continue down the form list.

Figured maybe my Field was wrong but then I changed the CALCULATION to:

If( IsEmpty(Quantity), 4, 6) * 2

This continues and shows up in all fields.

My questions are:

1. It seems when I do a relationship in a calculation it doesn't continue down to the next item only the first line item.

2. What calculation can I use to PRINT Inventory::Price WHERE Form::Size=Inventory::Size and Form::Description=Inventory::Item???

Thanks in advance

-Chris

[ernst]

Hey Chris,

Seems you want to use two fields on each side of the relation between your order database and inventory database, 'size' and 'description'.

You can do this by defining a calculation field in each database that combines the fields, and use these for the relation. I would call these fields 'cSizeAndDescription' and the definition should be:

Size & " " & Description (or 'Size' and 'Item')

Don't forget to set the calculation type to "Text" on each calculation field!

As to your questions:

1. No, a relationship in a calculation works on all records in a list, not only the first one.

But if you mean that the calculation finds more related values that match, THEN it will use the first.

Explanation: if your order form would use a price calculation and there would be more then one inventory records that match, then it will use the first.

2. Re-reading question two I think I've already answered that one.

Hope this helps,

Regards,

Ernst.

[fmakerNewbie]

ernst,

What I am saying is When I setup Product Unit Price as Calculation

and inside that calculation i do

if( IsEmpty( Quantity) , 0 , 1) * Product Description MATCHES Inventory Item : Price

I have a list of 5 items on the Form

Item 1 inserts the price

Item 2-5 don't do the calculation.. its like the calculation doesn't work.

not that i get the same result for all 5 entries..

I will look into the first part of your reponse and see how that works

but maybe now you better understand the problem I have been running into with the Calculation of Product Unit Price.

[ernst]

>>Item 2-5 don't do the calculation.. its like the calculation doesn't work.

You could try to put the related price field (from inventory) in your order form list, and see if there is a related value for Item 2-5. I think something with the relation is wrong.

You could also see what happens if you duplicate item 1.

I mean, see if the duplicated item works or not.

Good luck!

Ernst.

[fmakerNewbie]

If I try to "Specify Field" to be "::Price" from Inventory that doesn't work either.

If I have the same exact entry as line 1.

That doesn't work either.

Seems to be that only line 1 wants to work.

I have checked that "repeat" is on..

I am lost why this is happening.

[ernst]

Hey CHris,

Try to zip the files and attach them so I (or others) can have a look.

[fmakerNewbie]

Check your email

[BobWeaver]

You're not using repeating fields are you?

[fmakerNewbie]

Yes I am bob..

Field Format

Repetitions

Show 5 of field's 5 defined repetitions

Use vertical orientation

[ernst]

Hey Chris,

If you go into field definitions in the 'catering orders file' and set the 'product unit prize' to auto-enter a lookup from the 'Price' field from the 'Product Description equals Description' relation then things start to work.

I'll do a little bit more fixing and mail the files back.

Regards,

Ernst.

[BobWeaver]

That's your problem. Only the first repetition will be able to access the information from the related file. Repeating fields are a holdover from the days before Filemaker was relational. Repeating fields have very limited functionality, and will cause you increasingly worse problems as you develop your database system.

You should scrap the repeating fields and use one record for each item being sold. This means that you will want to split your main file into two separate ones. A sales or invoice file, and a line items file. The invoice file would contain common information for the sale such as Invoice number, customer information, date, sale total price etc. The line items file would contain the information for each item sold on that invoice such as quantity, description, unit price, total price for the item, and of course, the number of the invoice to which the line item pertains.

You would then create a relationship between the invoice file and the line items file based on the invoice number. And finally, on a layout in your invoice file, display a portal to the line items file and include the quantity, description, unit cost and total cost fields. This will allow you to add as many items as you want (not just 5) to the invoice by just entering a new line in the portal. To get a grand total of all items in the invoice, you create a calculation field "Grand Total" with the formula:

Sum(LineItems::ItemTotal)

This assumes the relationship name is "LineItems" and the name of the Item Total field is "ItemTotal"

Hope this helps.

[ernst]

I agree with Bob that the repeating fields are to be avoided, though in this case the construction can work if the price in the order file is looked up from price in the inventory file and then used for the calculation.

But to make the databases stand out in the long run, I would suggest looking at Bob's suggestion and constructing something along the lines that he describes.

By the way, Chris, you can attach files to the messages in this forum so that much more people can see them and make suggestions!

Regards,

Ernst.

[fmakerNewbie]

all,

I am trying to understand this a little better.

This is actually a DB layout that someone else gave me to fix for them because it wasn't working. This was the way they had everything layed out.

I think I understand what you are saying.. but Im not quite sure so let me say what i think you suggest and you tell me where I am wrong.. and also if you can help answer the questions i have doing it this way.

Create the following Databases:

Order Print Form (combine everything together for a nice layout)

Order Items (these are inserted but extra field being PO#(PO# is gotten from Order Print Form)

Department Inventory

So order would be for user to:

Click New PO on the "Order Print Form"

Enter the date and customer information

Then click "Add Items" (this will open the Order Items DB)

The Order Items DB will open up with listing ONLY the current PO#

(ie. Fields on the Order Items DB will be PO#(hidden), dept, qty, size, item, price per unit, total per line)

Am I correct?

I think this is the way you were suggesting.. having one big order form but just have PO# as a field.. so have only current PO# showing.. will give you only current items to be purchased.

How do I get the PO# from the other DB?

How do I create a "Add Items" on the Order Print Form page that will open "New Record" on the Order Items DB?

Also few more things that will need to have..

The Order Print Form will need to break down in sections

ie.

Department 1

------------

item1

item2

item3

Department 2

------------

item1

item2

item3

Department 3

------------

item1

item2

item3

Then also the Order Print Form DB I will need to create multiple layouts for the following:

Click on Tab..

Opens layout that shows all Items Ordered from a Department for a day

ie.

If user selects Sat Aug 16,2003 it will generate a list

of all items from all PO's that are purchased from that dept.

But combine same items

ie.

Dept1

12 6' American Hero(s)

12 was generated because it is total ( (2-PO#1, 3-PO#2, 4-PO#6, 3-PO#10) )

Is there anyway to do all this?

Or should I just keep the existing layout ernst?

Ernst, will the existing layout be able to do all these features?

How much time will need to be invested to get all this working?

I appreciate all your help.

If I need to redo all this to get rid of "repeating" then I won't even bother to post my current files.. i would rather start from scratch if you guys can help me.

Thanks

[BobWeaver]

Yes, you are correct with your interpretation of the files required, but for reasons that I will explain later, "Order Print Form" is not a good name for the file that contains the general PO information. I would just call it "Orders."

How do I get the PO# from the other DB?

How do I create a "Add Items" on the Order Print Form page that will open "New Record" on the Order Items DB?

If you check the "allow creation of related records" box in the relationship definition, there will always be a blank line at the bottom of the portal. As soon as you start entering data into this blank line, a new record will automatically be created in the items file with the PO# field automatically filled out (assuming that you are using PO# as the key field in the relationship).

The Order Print Form will need to break down in sections

ie.

Department 1

------------

item1

item2

item3

Department 2

------------

item1

item2

item3

Department 3

------------

item1

item2

item3

You should be printing the order from the Items file, not the Orders file. That's why I suggested changing the file name. It is always preferable to print from the child file because you can create print layouts with subsummary parts and multi-level sorts that are not possible if you try to print with a portal in the parent file. So, you set up a subsummary part sorted by department, and show the department in that part. Then show the item info in the body part. To print, create a simple print script in the items file, and call this as a sub-script from a main print script in the parent file. This way, the user doesn't actually have to switch files in order to print.

Then also the Order Print Form DB I will need to create multiple layouts for the following:

Click on Tab..

Opens layout that shows all Items Ordered from a Department for a day

ie.

If user selects Sat Aug 16,2003 it will generate a list...

Again, these layouts should be in the items file where you can easily set up these summary report layouts. You can create report scripts in the items file, and call them from scripts in the main file.

[ernst]

Hey Chris,

If you really want to dive into this, you should follow the 'roadmap to inventory' that Bob has laid out for you in the post just above this. With the existing layout you will run into trouble if (for example) you try to make a report that summarises all ' 6" American Heros '.

On the other hand this will not be an easy task; you'll have to dive into learning the ins and outs of Filemaker and construct the database from scratch.

It is a bit like renovating (is this English?) an old house. You can either put a bit of plaster here and there or take out floors and walls and spend years rebuilding it.

It's hard to make an estimation how much time making these databases will cost, but if you are a relative novice with Filemaker I would think that it would take at least a few months to get it sort of working. Question is if you want to invest so much time, if you get paid for doing it, and if so, if the people paying you are ready to invest so much.

I wonder what Bob's opinion is about this.

Regards,

Ernst.

[BobWeaver]

Ernst,

I think you have already brought up the most important point that it will be nearly impossible to create the kind of reports that are required (Items grouped by department) if repeating fields are used.

I haven't seen Chris's files so I don't know how much work has already gone into them, but I don't think it is a very big job to create separate line items and orders files. At the very least, the existing file could become the orders file, and its repeating fields could be exported to the lineitems file (separating them into 1 item per record). That would be a good start, without wrecking too many existing layouts. The repeating fields in the orders file layouts could then be replaced with a portal to the line items file. The result should be as functional as the old setup, and anything that is added later will be much easier.

[fmakerNewbie]

All,

Okay starting from scratch can you tell me how this will work.

1. Create "Department 1 Inventory.fp5".

2. Define 3 Fields (Size, Item, Price)

3. Create "Items List.fp5"

4. Defind Fields (Order#, Dept, Qty, Size, Description, Unit Price, Amount, Subtotal, SalesTax, GrandTotal)

How do I say:

if Dept = DEPT1 then Description=Department 1 Inventory::Item

to make Description be a drop down of only products from the Department 1 Inventory.. and then so on as I add other department inventory db's.

5. Create "Orders.fp5"

6. Defind Fields (Order #, Date, Day of Week, Event Date, CustomerName, Street, State, PostalCode)

How do I start with Orders -> Get Auto generated Order#, Enter customer info, Click Add Items (Which executes ItemsList.fp5... but doesn't show old Order# Items.. Only Current Order# (which to start would be blank), Then Click Done after all Items have been added and go back to Orders.fp5 where it will pull in all info WHERE Order#=LineItems::Order#

I think that explains the applications needs in the form that you suggest as a relational DB.

Thanks in advance for all your help.

-Chris

[ernst]

Hello Chris,

I think you should make a general inventory file. Not one specific for one department.

Just add an extra field 'dept' to inventory. In the 'Inventory.fp5' database make a value list with all departments, displayed either as menu if each item has maximum one department using it, or as checkboxes if any inventory item can be used in several departments.

Then we come to your first question. In the 'Orders.fp5' database define a relation from 'dept' to 'dept' from 'Inventory.fp5'. Then define a valuelist with the 'Use values from field' option ON, and set to show values from 'dept' when the relation created before is valid.

As to your second question: Just set your 'Order Number' field to autoenter a serial number.

And your third question: In your ItemsList.fp5 file you should also have an Order Number field. In 'Orders.fp5' define a relation from 'Order Number' to the same field in 'ItemsList.fp5' with the 'Allow creation of related records' option on.

The fields from 'ItemsList' could the be displayed via a portal in "Orders.fp5' and new items could be generated by entering data on the last line in the portal.

Pfffff time for a break. Hope this makes sense.

Ernst.

[fmakerNewbie]

Hey guys sorry its been so long...

I am attaching 3 files

1. Catering Order Form

2. Line Items Form

3. Master Inventory List

I need to find out:

1. How to create a portal from Catering Orders -> Line Items

2. Automatically Enter the CURRENT Order # from Catering Orders into Order # for Line Items.

3. Automatically Enter the Event Date and Event Time from Catering Orders

4. In "Portal Mode" I need only current Order # Line Items to show in Line Items.. (so if no items were entered yet.. looks empty.. ).

5. Once Line Items are entered.. be able to go back to Catering Orders Form.. and it will do Subtotal, Tax, and Grand Total.

Let me know if this layout is correct and how to get the other parts working..

I am under a pretty tight time crunch... (managers bitching they want this done.. and I am trying to get this ready for them.. in a pretty good working form.... edits can be done later.. but i want to get this basic functionality done).

Thanks so much..

Catering Orders.zip

[ernst]

Hi Chris,

I fiddled a bit with it (your files).

Have a look and see if this is sort of what you mean. Made an extra 'product code' field that's used to lookup the products and corresponding prices.

This could be more elaborate, but since the inventory list is not

Catering Orders ERNST.zip

[fmakerNewbie]

Thanks ernst for looking at this..

I modified a few things..

Made the auto date and event time in Line Items match the one on the catering orders sheet by Where Order# equals Order#.. Use Event Date , etc.

1 thing i don't completely understand is the product code.. isn't there another way of doing this?

Or a way of opening a POP-UP of Line Items to enter the Items and then go back?

Just trying to understand the reason for having this.. which in essense becomes the Name the person entering the order will have to learn..

Thanks again

P.S. also fixed the messed up sales tax / deposit calculation that was there

[ernst]

>>1 thing i don't completely understand is the product code.. isn't there another way of doing this?

>>Or a way of opening a POP-UP of Line Items to enter the Items and then go back?

Yes, there are other ways to do this.

A Popupmenu with descriptions would be one. But relationships only work on the first 60 characters, and you had descriptions in your MAIN INVENTORY file that are 107 charcters long, even excluding the 'tray' description. So you would have to make sure that description + tray would always be shorter then 60 characters.

Another possibility would be to use a unique numerical productcode for the relation and lookup, and select the product via a script that takes the user to a list layout from MAIN INVENTORY, and then returns to the order database and inputs the product code there. A bit more work but certainly possible.

The third method -the one that I used- is with a unique productcode that is a sort of short description. If you organise these productcodes in a consistent way then this can also be a usuable way to achiev the desired result. And it is reasonably fast & easy to implement.

Most important things are IMO

A. to understand the mechanisms that Filemaker offers to achieve what you want

B. have a clear idea about the process that your database has got to automate.

regards,

Ernst.

[fmakerNewbie]

ok.. i guess I am understanding.

to get away from "repeating" values.. that is why i tried to make it relational.

only thing i thought was going to be the way to do it (different from yours) was...

you would click a button called "Add Items" which would open a layout of "Line Items.fp5".

Once opened it would allow you to add items at will which is currently what the file does as standalone.

Then there would be a "Done" button on the top of "Line Items.fp5" that would "Hide" Line Items bringing you back to the original "Catering Orders.fp5" file.

I was expecting at this point the portal would "source" in the changes you made to the Line Items file as a list. This would be done without the need for "ProductCode" I think.. but just didn't have any idea of how to go back and forth between files "sharing data" (Meaning sending Event Date, Pickup Time, Order #, etc -> Line Items.fp5 and then on the way back sending all the items -> Catering Orders)

This way works.. just need to learn the list of "ProductCodes".

Thanks for your help Ernst.. it is much appreciated..

Thanks Bill for your suggestion to stop using repeating values and start over with a relational db...

Thanks again.