Detecting text

[madman411]

Ok guys, here it goes:

I'm having a problem getting my database to detect if there is text in one field, and if there is, to jump to the next.

What I have created is a transactions database for school lunches - as my dad is an IT teacher at a new school, however he knows nothing about FM.

At the top of the screen there are buttons for the designated lunch item (i.e. "Hot Lunch" "Cold Lunch" "Fries" "Salad" etc..) When the button is clicked, the text (Hot Lunch, for example) is entered into a field, and the price is entered next to it (see screen shot.) However, when this database is going to be used, the staff member can't be typing in prices. He/she should just hit the button and it should appear.

What I want is that when a button has been clicked, the info will go into field 1 and the price will go into the price field. When the staff member clicks another button (say the student got a milk with their hot lunch,) FM then chooses the next blank field to enter "Milk" and then the price.

I know this sounds confusing. Hopefully the screen shots of what I have so far will help.

The math portion of the database works flawlessly. It adds the prices and deducts it from the student balance without a problem at all. It's just the "auto-detect" (if you will) that I am having problems with.

If you need anymore information, please just ask! I'm ready to finish this project!

Screen shots:

1. Purchase Fields

2. Meal Buttons

3. General Screen Shot

Thanks for any help you can give me!

[Fitch]

Using separate fields for your items is the textbook wrong way to design a database. (I'll bet you have all your prices hard-coded into your scripts, too. There is a better way!)

What are you going to do if down the road somebody wants to find out how much milk was purchased last month? Good luck!

The right way is to create a related Line Items ("purchases") file. Display the line items in a portal instead of your current "purchases" fields.

Not only will this save you major headaches in the future, but it simply solves your current problem: all you have to do is go to the last portal row.

[madman411]

I see what you're saying.

How can I use the buttons to put the data into the portal rows though? Would I need to make two portal selections to seperate the prices and item descriptions? I've only ever glanced over the portal option because so far I've never had to use it.

Sorry for being a pain! I appreciate it though.

[John Mark Osborne]

It looks like you have a separate field for each item. Unfortunately, this makes the task a little bit harder. I would recommend using another table (this is a file in version 5) and a portal to enter purchased items. Not only will this make it easier to work with the solution but it will allow you to easily create reports on how many of each item you have sold in a time frame.

The easiest way to get your current solution working with a script, is to write a conditional statement. There are ways to make the script more dynamic and adaptive but this will be the easiest method given the limited number of fields you have. Here's what your script might look like:

If ["IsEmpty(Field1)"]

Set Field ["Field1", ""Milk""]

Else

If ["IsEmpty(Field2)"]

Set Field ["Field2", ""Milk""]

Else

If ["IsEmpty(Field3)"]

Set Field ["Field3", ""Milk""]

Else

If ["IsEmpty(Field4)"]

Set Field ["Field4", ""Milk""]

Else

Set Field ["Field5", ""Milk""]

End If

End If

End If

End If

Unfortunately, you have to attach this conditional script to each scripted button.

I would recommend seriously considering tracking the sold items in another table/file by using a portal. I would then use a popup menu in the portal to add the items sold. It will make a much better interface than a bunch of buttons and much more flexible when you need to add or edit an item. And, reporting will be much easier.

If you attach your solution to this post, I'd be glad to modify it to show you how this works.

[Fitch]

How can I use the buttons to put the data into the portal rows though? Would I need to make two portal selections to seperate the prices and item descriptions?

The buttons would work just like before, you'd set one field to the description and one field to the price. The difference is that those two fields are in a separate file, and a new record is created for each item. But you have actually simplified things in a way because instead of 5 price fields and 5 description fields, you only have one of each -- from 10 fields down to 2!

One other thing: typically you would create a Products file that has a record for each item that you sell, along with its description and price. This makes it easy to add or update the items. Each item would have a unique ID, and what your button would actually do is set the ID in the Line Items record, and in Line Items you'd define your Price and Description to lookup from Products based on the ID.

Having said all that... John Mark Osborne just offered to work on your files! You won the FileMaker lottery!

[madman411]

that script works better than anything I've ever tried! Thank you!

I'm going to start working with this portal thing here pretty soon. If you can suggest a way that's easy for you to do, I'll go ahead and try it.

Thanks for your help, guys!

[madman411]

JMO, I have emailed you the database to the address listed on your profile.

Thanks again!

[John Mark Osborne]

I meant for you to attach the file to your post but no matter. I have attached the file you sent me via email to this post and will attach the updated version to my next post with an brief explanation.

transactions_database.fp5.zip

[Lee Smith]

Unless requested to do so by the other member, please do not send files off list (i.e. directly to another member).

Why?

Because this is a learning Forum, and others with a similar question can benefit also.

Because you will probably get a faster response on the Forum, as you don't know the schedule of the other member.

and Because there is often more than one way to accomplish the same thing, and you might find someone has a suggest that fits your need better.

This is one reason why the Forum works better than a Emial List, we have the ability to attach the files here. You can attach your files using the [color:red]Manage Files in the [color:blue]New Reply Screen. For additional help on how to do this, see the [color:blue]Frequently Asked Questions in the [color:red]Help Menu.

[John Mark Osborne]

Here is the modified solution using sound relational techniques. I added three files since there is a many-to-many relationship from student purchases (days.fp5) to purchase items (items.fp5). In other words, one student can purchase many items and one item can be purchased by many students. Therefore, you need a third table to join the students and items (trans.fp5). Your existing file has been transformed into students.fp5.

There is quite a bit to absorb here but keep remembering this will allow you to keep track of every purchase a student has made on every day they made the purchase. You will also be able to report on all the purchases in a particular month or a year using subsummaries.

If you have questions about the file, feel free to ask.

Transactions.zip

[madman411]

Thanks guys. I appreciate your help!