Help with a Script

[adam2552]

I am trying to make a script that will accomplish the following:

1)When I hit a button in one table (Table 1)

2)Switch to another table (Table 3)

3)Enter Find mode

4)Search by using a manually entered number

5) a)If no results create new record

If results display record

Any help on this would be appreciated. I am new to FileMaker can not quite figure out the If commands on there.

Thank you.

[LaRetta]

Hi Adam, welcome to FM Forums! Roughly untested, here's a script:

Freeze Window

Show Custom Dialog [ "Enter number" ]

# on input tab, specify field of global located anywhere in your file

If [ not number ]

Show Custom Dialog [ "No number specified. Process will end."]

Halt Script

End If

Go To Layout [ layout based upon table 3 ]

Enter Find Mode [ uncheck pause ]

Set Field [ table3::numberField ; globalNumber ]

Set Error Capture [ On ]

Perform Find []

If [ Get ( FoundCount ) ]

Go To Layout [ detail layout showing that record ]

Else

Go To Layout [ layout for new records ]

New Record Request

Set Field [ table3::numberField ; globalNumber ]

End If

UPDATE: But I wonder what table 1 has to do with table 3; anything? And I wonder if this number is something which should be auto-entered from within FileMaker? Or is this number a part number or something unique which must be generated from an outside source? Knowing the context of your question will help us with better answers.

LaRetta

Edited April 3, 2009 by Guest
Added update

[Lee Smith]

You have almost named the script steps envolved. I have a few questions though

Do you mean Layouts or Tables?

If Table, why are you going to a second table?

Go to Layout [ “Find Layout” ]

Set Error Capture [ On ]

Enter Find Mode [ ]

[ Pause ]

Perform Find [ ]

If [ Get ( LastError ) = 401 ]

Go to Layout [ “Form View” ]

New Record/Request

Else

Go to Layout [ “Form View” ]

End If

HTH

Lee

I made a quick demo of this script, note, I didn't do a second Table

Adam.fp7.zip

Edited April 3, 2009 by Guest

[adam2552]

Thank you two very much I was able to accomplish the task using your suggestions. To give you guys a little background on what I'm trying to do though:

I am creating a database to track products and customers. I need it so that when I receive a product, I can switch to my customer database to enter their info. Which is what I was stuck on. Thank you guys again.

[adam2552]

Well let me preface this with the big idea, I am trying to run a Used Tools store. I need to be able to access both customer's and product's data in the same database. I created two tables, "Product" and "Customer". The thing I really want to be able to do though is track which customer bought which used tool and create a list of all the used tools that a particular customer has purchased. For instance if one customer bought a Ryobi Power Drill, and a Ryobi Circular Saw. I want to be able to pull that up so I can give them a call when I get a Ryobi Jigsaw in-stock. This is the current script I have.

Go to Layout ["Customer" (Customer)]

Set Error Capture [On]

Enter Find Mode []

Pause/Resume Script [indefinitely]

Perform Find[]

If [Get (LastError) = 401]

Go to Layout ["Customer"(Customer)]

Show Custom Dialog ["No Record Found", "Create New Record"]

New Record/Request

Else

Go to Layout["Customer"(Customer)]

End If

Pause/Resume Script [indefinitely]

Go to Layout["Product"(Product)]

Insert Calculated Result [select; Product::Used In Stock; Product::Used In Stock - 1]

So what I have is access to the customer, I can create new records or access current ones. The thing I can not figure out is how to record the product info (fields: Product Name, Product Manuf, Product Price) from the Product table to the Customer table. Any ideas on how?

Thanks,

Adam Brown

[bcooney]

What you are describing is a simple invoicing solution. However, you're missing tables. You'll need:

Customer

Invoices

Invoice Line Items

Products

Manufacturers

You can then see from Customers a list of InvoiceLineItems (what they bought).

See from a Product, who bought it.

And, find in InvoiceLineItems by ManufacturerID, all items bought that match that ManfID, and go to the related Customers and send them an email or call them about the availability of another product from this Manf.

This demo might help you start off in the right direction.

InvoiceDemo.fp7.zip

[adam2552]

While that is definitely getting me on the right track, there are still a couple things I can't quite figure out. Ok so this is the idea here, on my Product Layout, I have a button that I want to press whenever I sell a Used Tool, this button triggers the script I just posted, now when I press that button I either find the customer, or create a new one. At that point I want to copy all of the data from the Product Table (Product Name, Product Manuf, Price) to another table. Now this "another table" needs to be specific per customer, so if Bob Jones comes in on one day and buys a tool, but comes back two months later and buys another they will both appear on "another table". How I am accessing this "another table" is from a button on the Customer Table. So if I search for Bob Jones I can immediately see what he has purchased from me in the past. While the Invoice demo set-up some ideas of how I could do that, I am just not that proficient with FileMaker yet. I am still not quite sure what exactly relationships do to be honest. Alright well if anyone has any ideas....

Thank you again,

Adam Brown

[Lee Smith]

I have merged your two topics because they are about the same problem.

[bcooney]

Let's discuss products a bit more. Do you sell a productID more than once? For example, let's say you have 3 Ryobi Power Drills in stock (ProductID = 1000). You can't put the customer's info directly in the Product record, because more than one customer will buy each of the three drills, right?

So, you need another table that stores the customerID and the productID, the price, etc. That's the line items table. It's the "other table" that you are instinctively thinking you need. It will have the CustomerID, the ProductID, the InvoiceID, price, extension. Typically, a customer buys more than one thing at a time. To unite all the line items, you need a parent: the Invoice.

You are creating a new Invoice from the Part layout. That's how you think, and it's fine. You've developed a script that starts a find in Customers, and uses the found or new customer for the new invoice. Great. You could have a New Invoice button on the Customer record, just like in the demo I posted. Then add the products to the invoice.

[adam2552]

Thank you again for helping, sorry if I'm not quite understanding what exactly to do here. In my database, customers will not be buying more than one product at a time. I don't need an invoice or a receipt for the customer. All I want to do is track their purchases. So when my script its executing I want a section of it to copy the contents of the product info, and paste the contents in the customers purchase history. If what you are saying is the way to do this than I need a little more help because I can't quite figure out exactly what to do here or what relationships I would need between the tables.

[adam2552]

I got it! Thank you for your help, definitely needed an "invoice" like you said. Now one other thing I'm running into which is probably an easy formula I'm just overlooking. How can I do this:

Ok I am also setting up a daily tracker to check what I sell each day, the tracker uses the same "invoices" that I made to get the customer's sales history to work. Now I have it set-up to display the fields, however I can not figure out how to get the totals of "Used Price". Is there a way to add values from the same field, but a different record to each other?

Thanks,

Adam Brown

[bcooney]

OK. To recap, you now have 3 tables: Customer, Products and Invoices. Invoices stores a foreign key to Customers and a foreign key to Products. Invoices also has a Date Created field, Qty, Price (looked up from Products), and an Extension (Qty*Price).

You want to total Extension? Why? You have said that there is only one product per invoice (that is why you did not create an InvoiceLineItems table).

If you are asking, "How do I total all the Extensions for a Customer?," then create a calc field in Customers that equals sum( cust_Invoices::Extension).