Find/search in a different window/table?

[Tony Diaz]

My main layout has a portal to itself that I've got a dynamic filter on so I can look for similar records and load information from them to the current record or load that record as the current record.

All is good there.

What I'd like to do is have a button to click on with whatever value is entered in the filter box, and have it switch the that trigger a find in another window.

From there then I can use a similar set of options to load data from that other window / un-related table/portal back to the main layout/window.

 

I'm using a separate database as a place to look up possible data from. This DB/table has a few hundred thousand records in it. So adding it to the dynamic filter or even dynamically filtering it is ridiculous.  So I'm using it as a local alternate source to query from, similar to scraping from a web page. So I want to query it and copy back from it instead.

 

I'm almost convinced it may be easier for me to actually just host that on a web database somewhere and actually query and scrape from it. At least that would happen quickly.

 

First thing, trying to copy from a portal row in another window, I'm not getting anything transferring over.

 

The button script is below. I don't get any of the field contents, but if I set the value to a static value I will get that copied over.  e.g. "TOSEC::TOSEC_Title" instead of TOSEC::TOSEC_Title. I'll get that exact text as expected with the quotes, but without I'm getting nothing.

# this button is pressed from a portal row on a window titled TOSEC with data from an un-related table.

Select Window [ Name: "Tranquility Base" ; Current file ] 
# 
Set Field [ Records::Item Title ; TOSEC::TOSEC_Title ] 
Set Field [ Records::Item Vendor ; TOSEC::TOSEC_Publisher ] 
Set Field [ Records::Item Intro Year ; TOSEC::TOSEC_Date ] 
Set Field [ Records::Item Description ; TOSEC Tags::TOSEC Tag full ] 

Similarly, on the reverse, I'm not getting the content of the search field on the main layout to get over to the second window either. Same kind of problem. I just get empty fields.

[comment]

I am afraid I couldn't follow your description. It sounds like you are trying to set an unrelated field to the value of a local (or related?) field. That is not possible. You need to set a variable to the value of the field that can be accessed from the current context, switch to another context (file/window/layout) and set the target field to the value of the variable. And of course, if this value is to be used as search criteria, you must enter Find mode before the last step, otherwise you will be modifying some random record in the target table.

 

[Tony Diaz]

The opposite, actually. (Well, at least in description)

I would like to search elsewhere, an unrelated table and bring values from 4 fields over to the local table/layout.  Probably means the exact same thing, I'm really thinking a web view and scrape might be a viable alternative. Seems whack though, that I'd have to setup a mysql / PHP solution on httpd somewhere along the line.  A list of returned values in row style like a portal does, and a scripted function to copy from said row. Even if that search takes place in a separate FM window and it goes between them I don't care.

If I have that other table setup with a relation I'm getting disc spinning hell when I make queries to it.

The behavior / workflow I'm trying to emulate is like an API search to some data source and then let me pick one record from the returned results to populate four fields.

Say I have books, I want to enter them into a local database,  I want the option to search another set of data and take in the values returned. Like I'm searching my own Card Catalog and copying data from it.

[comment]

11 minutes ago, Tony Diaz said:

If I have that other table setup with a relation I'm getting disc spinning hell when I make queries to it.

That's something I would look into first. Because a relationship is not that different from performing a find. If one is slow, the other is likely to be slow too. Is it possible you're trying to do this through portal filtering, instead of matching up fields in the relationship setup?

Anyway, what you describe should be quite simple to set up. Have your script set a variable to the value to search for, open a new window, enter find mode, go to a layout of the "other table", set the searched field to the search value, perform find and pause. Upon clicking one of the found records, set 4 variables to the values of the selected record, and close the window. Now you are back in your original layout, and you can use the variables to populate fields of the current record.

However, I am not convinced that copying data across the table is a good idea. You should be able to populate a foreign key field with the primary key value of the selected record, and display the 4 values directly from the related record in the other table.

 

[Tony Diaz]

When I first started I was using filtering. Though At some point I thought I went another direction.  Ideally, if I can have that portal show the results of a related table record, that is it.  It just seemed that I wasn't getting the right data showing in the portal after a search, or being able to copy the fields back.

I need to really nail down relationships, XOR, OR, NOT, AND and all that stuff better.

Feel free to tell me how brain dead I'm probably doing something.. 

That third column is a related values value list contraption.

Edited May 15, 2020 by Tony Diaz

[Tony Diaz]

8 hours ago, comment said:

However, I am not convinced that copying data across the table is a good idea. You should be able to populate a foreign key field with the primary key value of the selected record, and display the 4 values directly from the related record in the other table.

Okay, now this one clicked.

The problem is there's not always going to be a related record. It's kind of a "last resort" source of populating the fields in Records.

That is why I'm hung up on copying from the table, I'm after the actual values only.

Workflow:

Take an item from the shelf, look in Records to see if I have it, if I do, make the qty changes, notes, whatever to the record in Records.

If I don't have it already, search the TOSEC table, let me pick from the Portal rows, one of them that I will tell it to copy the contents of select fields over.  I have no interest in any kind of relationship with these records. It's purely for looking up data.  I could at any time drop that whole table and regenerate new data for it, whatever..

 

Set Field [ Records::Item Title ; TOSEC::TOSEC_Title ] 
Set Field [ Records::Item Vendor ; TOSEC::TOSEC_Publisher ] 
Set Field [ Records::Item Intro Year ; TOSEC::TOSEC_Date ] 
Set Field [ Records::Item Description ; TOSEC Tags::TOSEC Tag full ] 

So what I'm running into here is I'm getting nothing entered if I do that above.

But if I do:

Set Field [ Records::Item Title ; "TOSEC::TOSEC_Title" ]

Then of course, I'm going to get the literal table::field name.

 

 

[comment]

12 hours ago, Tony Diaz said:

The problem is there's not always going to be a related record.

What I meant is once you have selected a record from the search results, make it related instead of copying its data. But of course, that's not going ro work if you plan on dropping the other table, or replacing all of its data.

As for your workflow, I think I've already answered that. If the two tables are not related, you cannot set a field in one table directly to a value from a field in the other table. You need to load the source field's value into a variable, move to the layout of the other table, and then set the target field to the value of the variable.

 

[Tony Diaz]

So, to sum up some of the question, I need to think of a portal not as looking at data from another table,  but rather looking at data from another -related- table.

I've got it doing the search on the other window and bringing back the values now.

// Set Variable [ $Global_Search_Item ; Value: TranquilityBase::Global Search Items ] 
If [ IsEmpty ( FilterValues ( WindowNames ; "TOSEC" ) ) ] 
New Window [ Style: Document ; Name: "TOSEC" ; Using layout: “TOSEC” (TOSEC) ; Height: 920 ; Width: 1340 ; Top: 400 ; Left: 1400 ] 
End If
Select Window [ Name: "TOSEC" ; Current file ] 
 
Enter Find Mode [ Pause: Off ] 
Set Field [ TOSEC::TOSEC_Title ; TranquilityBase::Global Search Items ] 
Perform Find [] 

Now to finalize this, I'd like that search to be in TOSEC::TOSEC_Title -OR- TOSEC Tags::TOSEC Tag full.

Enter Find Mode [ Pause: Off ] 
Set Field [ TOSEC::TOSEC_Title ; TranquilityBase::Global Search Items ] 
Set Field [ TOSEC Tags::TOSEC Tag full ; TranquilityBase::Global Search Items ] 
Perform Find [] 

This is both fields need to have a match.  The scripting operators talk about the search criteria within one field, and no OR option is jumping out at me even there. The closest I can come up with is Extend Found Set, but still no OR ...

[comment]

2 minutes ago, Tony Diaz said:

I need to think of a portal not as looking at data from another table,  but rather looking at data from another -related- table.

Yes. 

 

3 minutes ago, Tony Diaz said:

I'd like that search to be in TOSEC::TOSEC_Title -OR- TOSEC Tags::TOSEC Tag full.

To perform an OR search, you need separate requests:

Enter Find Mode [ Pause: Off ] 
Set Field [ TOSEC::TOSEC_Title ; TranquilityBase::Global Search Items ] 
New Record/Request
Set Field [ TOSEC Tags::TOSEC Tag full ; TranquilityBase::Global Search Items ] 
Perform Find [] 

However, this does not agree with what you said later:

8 minutes ago, Tony Diaz said:

This is both fields need to have a match.

That describes an AND search, not an OR search.

 

10 minutes ago, Tony Diaz said:

The closest I can come up with is Extend Found Set

That is another way to get the same result.

 

[Tony Diaz]

9 minutes ago, comment said:

To perform an OR search, you need separate requests:

...and that gives the impression of checking the first returned set further against the additional criteria.

But lets try it anyway.

Enter Find Mode [ Pause: Off ]
Set Field [ TOSEC::TOSEC_Title ; TranquilityBase::Global Search Items ]
New Record/Request
Set Field [ TOSEC Tags::TOSEC Tag full ; TranquilityBase::Global Search Items ]
Perform Find []

Enter Find Mode [ Pause: Off ] 
Set Field [ TOSEC::TOSEC_Title ; TranquilityBase::Global Search Items ] 
New Record/Request
Set Field [ TOSEC Tags::TOSEC Tag full ; TranquilityBase::Global Search Items ] 
Perform Find [] 

 

"This operation cannot be performed because one or more of the relationships between these tables are invalid."

But those two tables are related. So that's not what it means..

Getting close, I suppose.

Edited May 16, 2020 by Tony Diaz

[comment]

3 minutes ago, Tony Diaz said:

"This operation cannot be performed because one or more of the relationships between these tables are invalid."

But those two tables are related. So that's not what it means..

Two tables can be related, and still the relationship can be invalid. What kind of field is TOSEC::TOSEC_ID?

 

[Tony Diaz]

Ah.. okay, one of the pair was Number, the other Text. I changed Number to Text and I'm still getting the same message.

[comment]

No, that's not it. There is a T ending to the line leading to the TOSEC::TOSEC_ID field, indicating that the field cannot be indexed (could be an unstored calculation field or a global field). Therefore the relationship is invalid in the direction from TOSEC Tags to TOSEC.

[Tony Diaz]

Well, I was trying to use the value of a global field as the search criteria.

Changing it to use $Global_Search_Item instead gives the same result.

Changing it to "$Global_Search_Item" (a literal search) or course returns no matches but no error about relationships either.

So the question is then how for me to get that value as a variable into those two fields..

[comment]

No, the question is what are you trying to accomplish by searching a related field in a child table. Normally, it would be to find parent records that have a child that meets the criteria. But when the match field in the parent table is global, such search males no sense: there is only one global value for all records in the parent table. So either all parent records have such child, or none does. Filemaker rightly rejects your request as meaningless.

 

[Tony Diaz]

Okay, that makes sense when explained that way.

But that's what it seems like I want to do.

I want to check for this term in this field or, if that value is in the other field, which may not even exist. Obviously I can do them individually.  Maybe I just have to do them one or the other. (two different scripts, the latter being an additional step)

Data wise, yes, what I'm looking for is a title most of the time. But in that related field I may have a model #, ISBN, UPC...  doing the filtering on the main layout, I can filter against fields individually, (OR), but trying to filter against that table that has 600K+ records is nasty, hence the lets just search for it -if- we need it.

Hmmm.. I could create a field that is a calculation of the value in the child table, in the primary table. wonder what that would be like indexed..

But then why store that stuff in a related table in the first place. ?!?! 

Like I said, I need to nail down exactly what/where/when you should or should not do a relationship. (and how to keep FM from creating duplicates in the related table. But that's for a different thread)

[comment]

I am afraid by now I have lost track of what this is about.

Earlier you gave the example of a book catalog. It makes sense to search for a book by its title. It also makes sense to search the titles of its chapters. It makes no sense whatsoever for a BookID to be a global value.