reflous

I have several records in inventory.fp5 that I want to link to invoices.fp5 by invoice_id. I want to be able to run the script to do this within the database purchase_orders.fp5. The records in inventory.fp5 that are to be updated with a new invoice_id must satisfy two conditions, 1) inventory.invoice_id is blank, 2) inventory.project_id = purchase_orders.project_id I've never had to update fields within multiple records using filemaker before and I'm not sure how to go ahead with it. Thanks for any help.

This work around works really well. The only problem I have with it is I'm using a value list in the field that holds the company_id and in that value list I have to use both the id and the name together in order to get the id into that value. I don't have to look at the ID anymore which is great, but when I select it I still have to select it by ID. Is there a way so that I can select the company by name and look at it by name but still hold the ref id in Database B?

Nevermind, I thought it worked, it still doesn't. Show only was already selected. It is strange, even though show only is selected, when I go to po_lines there are still 4 records found, but the record I care about is the one selected.

Post deleted by reflous

Thank you for the information but it didn't work. The reason is because my GotoRelated record is based on my relationship of po_id between purchase_orders.fp5 and po_lines.fp5. po_lines.fp5 has four records with the same po_id, so all four are returned and the values from the first record in the found set is used instead of the record selected in the portal.

I have three tables, purchase_orders.fp5, po_lines.fp5 and inventory.fp5 The purchase_orders table has a portal of po_lines set by a relationship on the field po_id. I have written a script so when the items ordered from the purchase order are received, the user can hit a checkbox and activate a script within the portal row that should add that item into the inventory database. Everything works perfectly except that no matter which row is selected the first portal row is the one referenced (set field 'temp' by po_line by po_id:product_id). I assume this has something to do with the way I have my relationships setup. Ideas?

That would be useful to add cities on the fly, but what if I don't want to add a new city, I just want to select a city that pre-exists in the city table?

What I want to be able to do is: have two tables, table2 makes up a value list used in table1. In table2 I have a primary key (some sort of unique number) and a value (the actual value I care about). I would like the value list in table1 to only list the values and not the primary keys from table2. I would also like to be able to add new records to table2 on the fly from table1. Can this be done? If that is too abstract, here is an example of what I'm trying to do: I have two tables contact.fp5 and city.fp5 in city I have a list of cities that can be accessed in a value list in contact. City holds city_id and cityname. The problem I'm having is that I can easily keep the two tables seperate (just use a value list of all values from a different table) AND select the citynames rather than city_ids, but then I cannot add a new city on the fly to the database.

Those portal examples were invaluable, thank you so much, it is exactly what I want to do.

Situation: Tables: contact.fp5 and purchase_order.fp5 I need to be able to select a contact key for the shipto, billto, soldby, etc fields in the purchase order. I have thousands (maybe tens of thousands) of contacts which I can sort through and limit some (only certain people are shipto, only certain people are billto, etc, but it doesn't limit things enough to make it easy to use, there iwll still be a lot to scroll through). So I've seen two main ways of accomplishing this posted to the forum and in example databases. These are: 1) To "go shopping" for a contact (this is my preferred way) by storing the purchase order primary key into the contact database as a temporary variable choosing the contact, and inserting the primary key for the contact table into appropriate field for the purchase order. I really like this way of doing things, however if I'm using a temporary variable, and two people are "going shopping" then they will be overwriting this variable and one person might select a contact for the wrong purchase order etc. I tried making a temporary/join table that would create a new record per "going shopping" but I couldn't then build a relationship between the temporary table and the contact database without a variable (same problem). 2) The other way I've seen is by using a searchable portal in a new layout. I haven't quite figured out how this is implemented yet, but I don't like it because I don't have all the functionality of searching/listing/etc that I've built into the contact table already. Any ideas? Jon

How exactly does filemaker automatically create the record and insert the contact_id into the note.db? Also, I can't use the contact_id because I also have tables like: invoices.db quotes.db purchase_orders.db that all need note references. So it would make sense to have contact.db contain an id for note_id and not have note.db contain contact_id

I have two tables: -------contact.db---------- contact_id (primary key) note_id (foreign key) --------------------------- -------notes.db------------ note_id (primary key) note_text --------------------------- So what I would like to do is have a unique note record created and somehow get that primary key into the contact table. This is probably an easy to do routine question but I've not been able to figure it out or find any related posts. I could make note_id = contact_id but that will cause other problems for me, I need a unique note_id key. Thanks for any help.