Want to Search by Text String, not Autonumber

[simishu]

A bit new to this, so hoping that someone can point me in the right direction.

Basically I'm looking to create a searchable database of games for my institution, but I'm initially having trouble even getting the data page to accept strings as an entry field.I've been looking through the help pages and forums and haven't found anything specific to what I'm looking for, so I assume there's some design issue on my end.

In terms of setting up the relationships, here's what I've done. As the attached image shows, I've created a series of linked 4 tables that house (I've got other fields as well, but I'm going to tackle that later as they're extensions of this issue):

1. Game Info
2. Dev Info
3. Event Info
4. A table to unite them all
5. 

Each developer, event, and game has been given an autoID, which are linked to the Details table via an integer. I thought this would work because developers can make multiple games, games can be sold at multiple events, and games can have different versions, etc. In the relationship settings for the Display table, you can see that I've set the Parent Fields to be the respective ID Field (the autonumber) and displaying it as the text value located in the second field of the relevant table (ie, Game_Title; Developer_Name, etc).

This all displays fine if I look at the Details table itself; the autonumbers have been replaced with the appropriate names, titles, events in string format. The issue comes when I'd like to make a user searchable datapage: I can't figure out a way to let a user search via string, since the fields are technically integers. I can have a dropbox that displays text, but that isn't really what I need. What I'd like (for now, step 1) is a field where users can search via a string and matches that contain said string will show. I don't care if it only searches one field for now (game or dev or event); once I figure this out with some direction I'll try to build on this for more complicated search relations.

Are my relationships messed up, fields missing, or am I overlooking something simple in the datapage setting page?

 

Thanks for any help you can provide.

[kpcollier]

Hi, @simishu.

In your table that connects them all together - is it populated with the different combinations of records already? (i.e. multiple games by developers, events, etc.) 

If this is the case, you can use the special field Autocomplete. Use the lookup table as the main table that connects them all together, and Filter by the field you wish. 

However, if say you are trying to filter by one of your other tables such as Events, AutoComplete will not work the way you want. It will autocomplete based on the Integer you input. But, if I am understanding your app correctly, I do not believe you would want to filter it straight out of the events table or something similar because I don't think it would give you the results you want. If your main table has a relationship to the other tables with the string value as display value, you should be able to use the values in your main table to AutoComplete your field. 

If this doesn't work, please message me so I can better understand your app!

[simishu]

Thanks for the reply, @kpcollier.

I'm still having some issues with this, and I think it must be related to how I've structured the relationships and the content in each table. For example, when I try to implement AutoComplete, it still only accepts integers, not strings, which is the original problem. So, let explain a bit more about the structure of the data since that may be the root cause.

As you note, I have a central table where all the information is populated. As shown below, I've recorded each entry in a series of integers that correspond to the AutoNumber IDs in their respective tables. So, The Game_ID column links to the Game_Details table, the Developer_ID does the same in the Dev Table, and so forth.

Right now, I have this as a child table that links these numbers and displays the content in the cells as text from another column (as I outlined in the OP), like so:

 

When I make a search form using the dropdown form element, I'm able to identify specific fields to reference, as below:

 

but not if I switch to AutoComplete:

The filter AutoComplete by field only allows me to choose the Developer_ID field, but not reference the developer name text string. It's the same issue as before, which makes me think that I've structured my data and their relationships wrong. So, let me see if I can be a bit clearer about what I'm trying to do, as any advice would be appreciated. I'll use the image of the table with the data populated as text as an example.

 

What I'm looking to do is have a search field where users can input either 1) a game name, 2) a developer name, or 3) event name. The result page would show a list of the games, devs, or events that match that string. As I've said, I can't get the search to accept strings in the first place and is where I'm at now. Basically, those three categories are what I need to design the searchable features around.

In these results, I'd like a few other things. Since games can be at multiple events and have different versions, I'd really only like one instance of the game to show up -- when you click for details, it could show these different versions and where they were at.  Currently, the Details table houses all the games and their various versions and events as separate items so performing a search using it as the base returns multiple hits in some cases.

As I said, I'm very new at this so any help and insight into how to organize the data or set up the search form would be appreciated...and thanks for the patience!

EDIT:

As a concrete example, say "3rd Eye" has been released at events A, B, and C with versions 1.0 and 1.1. Right now, each of these would be a discrete entry in the "Details" table, as below:

Game         Dev          Event      Ver.

3rd Eye      Dev X         A             1.0

3rd Eye      Dev X          B            1.0

3rd Eye       Dev X         C             1.1

 

If I search by game or dev, I only want one result to appear. But in details it would show these variations. I suppose I could include the event information in the game and dev tables across multiple columns as child elements themselves...? But that still doesn't solve the original issue of searching by text string.

Again, thanks for any insights!

 

[Vitalikssssss]

Hi @simishu,

I can suggest creating a View which would join your Tables Developer and Details in order to have the ability to search by the name of the developer (Developer_Name field from Developer table)  with Autocomplete form element. You can use a View as a data source for your report Datapage.

Please check this article for more insights: 

https://howto.caspio.com/tables-and-views/what-are-views/creating-a-view-to-join-tables/

Regards,

vitalikssssss

 

 

[simishu]

@Vitalikssssss thanks for the reply. I'm looking into this at the moment, and encouraged that this may be one way to do what I want. I'll keep you posted!

 

Update: Seems like the view layout worked, and I've been able to configure virtual fields to enable search across multiple criteria. Thanks for the help!