Jump to content

Find/search in a different window/table?


Recommended Posts

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.

Link to post
Share on other sites

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.

 

Link to post
Share on other sites

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.

Link to post
Share on other sites
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.

 

Link to post
Share on other sites
Posted (edited)

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.

1293703902_ScreenShot2020-05-15at15_53_30.thumb.png.660d3bb28c5c6b9baa5bf7aa544e6bfa.pngFeel free to tell me how brain dead I'm probably doing something.. 

That third column is a related values value list contraption.

Edited by Tony Diaz
Link to post
Share on other sites
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.

 

 

Link to post
Share on other sites
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.

 

Link to post
Share on other sites

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 ...

921963311_ScreenShot2020-05-16at16_31_59.thumb.png.89a708c5620cabc3bbce24478924d5fb.png

Link to post
Share on other sites
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.

 

Link to post
Share on other sites
Posted (edited)
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..

1309745086_ScreenShot2020-05-16at16_49_55.thumb.png.d5fa3d22c723112756ddd900d9efc982.png

Getting close, I suppose.

Edited by Tony Diaz
Link to post
Share on other sites
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?

 

Link to post
Share on other sites

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

Link to post
Share on other sites

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.

Link to post
Share on other sites

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..

Link to post
Share on other sites

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.

 

  • Like 1
Link to post
Share on other sites

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)

Link to post
Share on other sites

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. 

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By Spidey
      Hi,
      I have two table: Invoice and Customer.  I like to have the total of all the invoice for a customer between certain date in the Customer portal that show all the customers, but I got a error when I try to debug..
      ExecuteSQL("SELECT SUM(I.TotalAmount) FROM Invoice I JOIN Customer C ON I._kf_CustomerID = C.__kp_CustomerID WHERE date(I.InvoiceDate) between date(C.SearchFromDate ) and   date(C.SearchToDate )" ; "" ; "" )
      I have an error and couldn't figure it out.  Thanks...
      KC


    • By Todd Dignan
      I have a client that has been using a send email script step  that brings up the outlook email client on the desktop.  This as worked for years no problem.  It has stopped work on 3 of 35 computers within the last two weeks.  I talked with there IT personal and they have assured me that no updates have happened.  The actual error is -
      Microsoft Office Outlook
      Either there is no default mail client or the current mail client cannot
      fulfill the messaging request.  Please run Microsoft Outlook and set it as
      the default mail client.
       
      I have double checked with system default  and Outlook's settings.  Both are set to default.
      The client is using the latest version of office 360's and the latest version of FileMaker 18 advance. Both 64bit.
      Any suggestions are welcome.


    • By tbcomputerguy
      I have an excel sheet that controls bills of ladings for a forestry company.  In the example you can see that there is lots going on with this Bill.  It has a payperiod, mill, truck that delivered it, etc.
      I would like setup a database to monitor this.  The fields CT1, CT2, Skid1, Skid2. PROC1, PROC2 are all contractor numbers.  There are 6 contactors.  The percentages in each line are the amount of the volume they performed  In the third line there is a value in CT1 only...they get 100% of the volume.  I can figure out most of this, but am stumped on how I can monitor when a contractor does multiple jobs..ie in line one, contractor 5, cuts and skids.  All 6 contractors could be involved in one BOL. Each one of these jobs, cutting, skidding and processing each has their own respective rate of pay as well.   I think i need a way to break down each line so that I can produce pay summaries for each of the contractors.  I had started this years ago, and thought I asked in a forum, but can't remember where.  Nonetheless, they stopped using multiple contractors per load...Now they have returned, so I am back at it.  So if this is a repost from years ago I apologize.  
      Thanks in Advance
      tbcomputerguy
       
       

    • By dancer5678
      I am using Filemaker Server 18 on Windows Server 2012 R2
      Been using it for years with no issues
      Currently when I log in to the console it is very sluggish.
      When I get to the Dashboard it shows No databases, then it auto refreshes and the database list appears.
      Within 15 seconds of scrolling the database list to open files the screen refreshes. This situations is happening over and over in a loop.
      Any Thoughts on what is causing this issue?
       
       
       
       
    • By stevaroni
      I get an error 3 when using a script to Export Records via WebDirect. Using FileMaker Server 18 and have tried both Safari and Chrome both with same results. I have tried using the temporary path, desktop path, and documents path. I have tried using with the automatically open and not. I have tried writing a tab delimited and comma delimited file. Does anyone have ideas I haven't yet tried?

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.