Appraisal Software

[fossapp]

I am new to filemaker and am currently using FM7. I am trying to create a system for tracking clients, appraisals and items. I have 3 tables one is

CLIENT (client_id[serial, auto-enter], First_Name, Last_Name, Address, City, State, Zip, Phone, Email)

APPRAISAL (appraisal_id[serial, auto-enter], Appraisal_Purpose, Appraisal_method, client_id)

ITEMS (item_id[serial, auto-enter, Artist, Height, Width, appraisal_id, etc,)

I have created relationships between CLIENT:client_id & APPRAISAL:client_id

APPRAISAL:appraisal_id & ITEMS:appraisal_id

I then created a button on CLIENT that goes to APPRAISAL and Creates a new record.

The issue is that when I create this button it opens the appraisal layout but I can't get the APPRAISAL:client_id to fill in the with newly generated serial from CLIENT:client_id,nor can I get the item_id to work.

Am I going about this wrong or am I missing something? Thanks

[bcooney]

What you need to do is store the ClientID before you switch to the Appraisal layout and create a new record.

With FM7, you do not have variables (that came in later versions), so you can use a global field. In your New Appraisal script that is on the Client form, use the Set Field script step to populate a global field in Clients with the current ClientID.

Then, after you create the new record in Appraisals, use Set Field to populate the foreign ClientID with the global field contents.

[fossapp]

OK, so I tried your suggestion and am not getting anywhere.

My NEW APPRAISAL script reads:

Set Field [client::client_id]

Go to Layout ["appraisal"(appraisal)]

New Record/Request

Set Field [Appraisal::client_id]

what am I missing?

The realationships are set to have

Client::client_id=appraisal::client_id

appraisal::appraisal_id=items::appraisal_id

Thanks for your help

[fossapp]

So I figured out how to Create a new appraisal and have the client_id field populate with the correct ID, but I am having zero luck continuin to the item layout and having the appraisal_id populate. This may not matter but it seems like it should.

A client can have many appraisals and an appraisal can have many items

Every item is 100% unique but I will need a way to group items into a specific appraisal.

According to the instructions on creating a global field it can only be used for one field. How do I have 2 global fields? Or how should I go about this.

[bcooney]

You have a standard Client>Invoice>Invoice Line Items setup, except your terms are Client>Appraisal>Items.

On your Appraisal layout, do you have a portal of Appraisal items?

Have you looked at any standard Invoice templates? It might clear it all up a bit.

[fossapp]

I agree that the invoice set-up is comparable, I am having trouble finding any invoice templates to refer to can you point me in the right direction?

[bcooney]

Here's a demo I did a few months ago. It's not pretty, but has the correct setup.

InvoiceDemo.fp7.zip

[fossapp]

Thanks for all your advice, I finally discovered that the problems i was having was that not only were the graphics an issue using FM7 on a Windows vista machine. But the whole field/table relationship gets screwed and and doesn't just not function but returns incorrect data, very frustrating. All's better now and I am on my way to a nice little database, thanks in part to your replies.

[fossapp]

I am working on my appraisal program and have a question about your invoice demo that you sent me.

Why do you need a separate table for Invoice Line Items and Products, is this to allow the sum fields to be for items in one invoice only?

Thanks

[bcooney]

Your situation may be different, in that you may not have a master list of products that you sell.

However, since Invoices have many Products, and a Product can be on more than one Invoice, you need a join table (Invoice Line Items).

Yes, the sum fields only calc the current Invoice record's Line Items.

[fossapp]

I don't have repeating items (or products) each item is unique. I am trying to figure out how to have a sum(items::ext_value) calculation limit the calculation to the ext_value items that are on one appraisal not every ext_value that has been entered. Any thoughts?

[comment]

Your situation is not entirely clear to me: do you do repeated appraisals for the same client? If yes, how do you want to handle items already listed for this client? Obviously, some items may not exist anymore, and there can be some new items. If you do not want to re-enter items that have been appraised in the past, you must have a join table to link items to appraisals (one item can be appraised many times, one appraisal appraises many items).

[fossapp]

I'll try to explain.

I will have many clients any one of which could become a repeat client.

Each client hires me to appraise some items.

Each appraisal consists of one or more items.

By definition every item is absolutely unique.

If the same client hire me to appraise an item one day and comes back years later for an updated appraisal I would want to open that client listing (the original entry) add an appraisal to that client (so this client now has 2 appraisals listed in their account) and add a new item (not a copy of the original item)

In reality it is rare to appraise the same item for the same client twice.

My sticking point is what I need to do to have a field in the appraisal layout which adds up the values of each of the items attributed to that appraisal.

Thanks

[comment]

it is rare to appraise the same item for the same client twice

OK, then things are much simpler, and the structure you have described in your first post should be quite adequate.

To answer your original question:

I then created a button on CLIENT that goes to APPRAISAL and Creates a new record.

You must take the client's ID with you, when you go to the Appraisals layout. In the button definition, define a script parameter as:

CLIENT::client_id

then have your script go to the Appraisals layout, create a new record and set the client_id field to Get ( ScriptParameter ).

You can do the same for items, or (probably simpler) allow the relationship between Appraisals and Items to create new records on the Items side, and just enter the items into a portal on the Appraisals layout.

My sticking point is what I need to do to have a field in the appraisal layout which adds up the values of each of the items attributed to that appraisal.

A calculation field (result is Number) =

Sum ( Items::Value )

will do it. This field needs to be in the Appraisals table. You can also define a summary field (Total of Value) in the Items table, and place it on a layout of Appraisals.

[IdealData]

Sadly FM7 was quite "buggy" and was released way before Vista - so it's not supported.

The earliest release for Vista was 8.5.2

[bcooney]

All this functionality is in the demo I posted.

[comment]

That was so last year...

[bcooney]

True. And it doesn't use script triggers and browse mode sub-summaries, so what is it good for?