Auto-fill One Field With Text from Two Fields

[cramps]

Basic question, but I haven't been able to figure it out from reading previous threads.

I have a Purchase Orders table and a Products table.

I've figured out how to create a drop down list consisting of the catalog numbers of my products for selecting when making a new purchase order.

I would like both the Artist and Tile fields from my Products table to auto-fill in the Purchase Order table's Description field based on what I've selected as the Catalog Number when filling in a new PO.

Any help is very appreciated and admired.

Edited November 17, 2008 by Guest

[Fitch]

Set the auto-entry options for the Description field to a calculation:

Products::Artist & ", " & Products::Tile

Welcome to FM Forums!

[cramps]

Oops, I had a typo there, I clarified the post now to read that I need the information in the Purchase Orders table to be pulled from the Products table automatically, based on what's in the Catalog Number field (also pulled from Products).

Previously the post had stated everything was located in Products.

I tried that formula, but it didn't produce any results.

I'm not sure how to express a sort of "If" Purchase Order Catalog Number = UW1 then Description equals "Artist and Title" info from UW1 in Products table.

and thank you!

[Fitch]

See if the attached file helps.

PO.zip

[cramps]

Hi,

This does help. Thanks for illustrating.

However, now a new problem has surfaced. The auto-fill is surprisingly inaccurate if I use another row of Catalog Number, Description within the same Purchase Order. It usually just continues to enter the same description from the entry, or will choose one at random, seemingly regardless of what Catalog Number is entered.

[comment]

If your order can have multiple items, then you need THREE tables. See the example in Help > Working with related tables and files > About relationships.

[cramps]

Ok, thanks. What this does is tell me I need to create a Line Item table. I don't see an example beyond that. I don't understand what that might entail. As of now I'm not sure how I would be able to do this type of process in a Line Item table if I can't do it in my Purchase Orders table. Any further help in this direction is appreciated. I'll do some digging and see if I can make sense of how to do this.

[comment]

See if this helps:

---

keywords: Invoices demo

InvoicesDemo.fp7.zip

[Joe_Schmo]

Ok, I think that will work it's just going to require an extra table/layout. I currently do not have an equivalent table to the "Line Items" one in your example. The way it all behaves seems like what I'm after, I'm just trying to see how it's all set up.

Why are the "Qty" and "PriceLU" fields on the "Invoices" layout set to Dropdowns but the 'display values from' option is set to "<unknown>"? Is this how you achieve the populated value that is still editable without affecting the source?

[comment]

Why are the "Qty" and "PriceLU" fields on the "Invoices" layout set to Dropdowns but the 'display values from' option is set to "<unknown>"?

It's a mistake - both should be formatted as 'Edit box'. However, the functionality is unaffected by this.

Is this how you achieve the populated value that is still editable without affecting the source?

No. The PriceLU field in the LineItems table is a lookup - that is, it copies the data from the Price field of the related record in Products.

[Joe_Schmo]

Ok, that should provide the functionality I need without affecting the original record. Thanks!

[Joe_Schmo]

What's going on with the Product name in the portal on the invoice? All the other fields on the portal are displaying data from the "LineItems" table and if I double click them I can edit the "Field/Control" menu settings. But the product name in the portal has a greyed out "Field/Control" menu and double clicking it brings up some type of edit box that I haven't seen before that controls the source data, which contains "<<Products::Product>>". How/why is that field different?

[comment]

See:

http://www.filemaker.com/11help/html/create_layout.9.24.html#1028554

[Joe_Schmo]

Ah, Merge field. I knew I had seen the << >> somewhere before. So that explains the "how" the product field is different, but why is a merge field used there instead of just adding another field to the portal?

[comment]

It's coming from the Products table, and users are not supposed to modify it.

[Joe_Schmo]

Ok. Well, I've copied the setup you provided as best I can as it related to my database, but I'm still missing something. The merge field on the portal is not populating when choosing the "Product ID" from the dropdown. I've checked the relationships and they seem to be set up the same as yours. The merge field is there because if I change the fill color in layout mode then I see the blank colored field in browse mode, it just doesn't fill in the value corresponding to the Product ID chosen.

With your example, it would be like choosing "7" as the Product ID for one of the portal records in the invoice layout, but "foxtrot" doesn't show up for the "Product". The same goes for the regular (non-merge fields) I added to the rest of the portal row, but I haven't worked with that too much yet. Still trying to figure out why the merge field won't show up.