Searching a value entered by a user

[spand]

Hi,

Im trying to solve something like this. User is fillin up a form. When hi enteres a value to a field with product serial number I want next to this filed print out matching name of this product. The name is stored in a diffrent table. Please suggest something. 

[doughemi]

Create a relationship between your form table and product table: form::formProductNumberField = product::PruductNumber. 

If you already have a relationship between the two, create a new Table Occurrence (TO) of the product table and create the new relationship between it and the form table.

Place  the product::product name field on the form. 

As soon as the product number on the form matches the product number in the product table, the product name will instantly appear on the form.

[spand]

Yeap, that helped. Thanks.

Another question. Now, when I have a portal made of fileds of 2 tables I noticed one thing. Fileds of the jointed table are inavtice till I enter something in a field from the main table.

[doughemi]

I'm not quite sure what you are asking, but it's true that until there is a valid relationship (the primary key is complete and matches the foreign key in the portal table), the portal will be empty.

Can you describe your problem in greater detail? 

[spand]

Pls check the vid https://screencast-o-matic.com/watch/cFVvrDoxna

I have a drop down list in the first column which i inactive till I enter quantity in the second one.

[doughemi]

It's impossible to determine what is happening without context. Can you attach your file, or a clone with a few dummy records? Also please describe the desired behavior.

[spand]

You will find attached my file. Pls go to Layout "Zamowienia". To issues: 

• first filed is inactive till I enter quantity
• third field should print name "nazwa" but it doesnt (when I choose kod_mnh from the first one) 

erp_prohan.fmp12

[doughemi]

The reason that the field does cannot be accessed is that there is not a valid relationship to the line items table yet (until a field is populated). 

One way to work around this is to turn off the "allow creation of new records..." option in the relationship dialog and script the creation of a new line item instead. A "New Item"  button executes the script.

Please refer to the modified copy of your file attached.

 

erp_prohan_dh.fmp12

[spand]

Thanks a lot!

[spand]

Hi doughemi,

 

I've just noticed one thing in file you have provided. When I want to add new products to new "zamowienie", products are added table "zamowienia_pozycje" (what is correct) but also to table Produkt. 

[doughemi]

Yes, that would require additional steps in the script. Actually, I would consider creation of new products to be a separate operation, instead of being part of the invoice process. But I have no knowledge of your work flow.

[spand]

Doug,

hmm, ok, I will try to rework that adding new producta. However, could you add those steps to avoid adding products within this script. It’d be very helpful for me to learn.

[doughemi]

I think that a new product code and name  typed in to the invoice is added to the Prodkt table. It is in the sample I sent you.

[spand]

Doug,

 

yes it does. What in wrong  I dont want to add new products in this way. I just want to add this product id to zamownia_pozycje table.

[doughemi]

I'm afraid I don't understand how one invoices a product one doesn't have. This has gone well beyond a standard invoice solution. It would require a much fuller understanding of the actual workflow than I have.

[spand]

Doug,

I think Im making you confused and I dont want to waste your time  If you dont mind I'd like to reset this conversation with a diffrent example (attached). I simply want to understand how to use database relationships without using "allow creation of new records." Workflow in the example is as follow:

• I have 3 tabels -> orders, products, order_deatils
• I want to create within an order its details. 

I want to learn how to access and use date from products table (product name, product price) when user in on Order details layout. That's my problem from the early beginning.

example1.fmp12

[Lee Smith]

Is this what you mean?

I changed the of name Date to ts (Timestamp)  and created a new field Getasdate to represent the function used for this.

example1Mod.fmp12