Relationship "contains" OR "equals"

[AaronR]

hello all.

This is my first experience with filemaker and my first post.

I am using filemaker 11 to create an asset database. I created it from scratch and one of the big things I wanted to have was the ability to relate some CSV-based tables that get downloaded from some asset tracking solutions that we use.

I have a table "hardware assets" that is related to a table called "cva_assets". CVA assets contains information from our leasing company. My plan is to download the leased asset list from them and place it in a location where filemaker would be able to do a recurring import. That way, I would easily be able to populate lease information for the asset. There is nothing in "cva_assets" that gets changed by me.

I am relating these tables using the serial#. My problem is that the serial numbers in "cva_assets" sometimes do not match. It's always an added S to the serial#. Eg. If a Serial# is 123456, the serial in cva_asset would be S123456. Again, this is just for some of the serial numbers. Other serials in the assets table do correctly start with an "S".

How should I try to solve this?

Thanks!

Aaron

[comment]

If I understand this correctly*, you should define a calculation field in your table (result is Text) =

List ( Serial# ; "S" & Serial# )

and use it as the matchfield for the relationship to cva_assets.

---

(*) it's not quite clear which table has the exceptions.

[AaronR]

Sorry for not being clear.

The table with exceptions is the CVA_assets table. If I define a cauculation (which the above seems correct) then won't it then break the relationship for the assets which don't have the prepended S? If all assets had the S, then it would be easy. Unfortunately just some of the assets that have the issue and match in every way except a prepended s. The rest match the serial# exactly. I guess I'm trying to do the following"

"display related data from cva_assets that contain the current record's serial# in the table hardware_assets". There should only be one match. I'm not changing data in cva_assets. I do directly enter correct data in hardware_asets and have a layout which displays minimal info about the asset (which I enter) additional lease details on a tab that is populated by the relationship.

[comment]

Assuming you are entering the serial without the S, the calculation will match records with OR without the S. For example, an entry of "123456" will match both "123456" and "S123456". An entry of "S123456" will match "S123456" and "SS123456".

[AaronR]

Assuming you are entering the serial without the S, the calculation will match records with OR without the S. For example, an entry of "123456" will match both "123456" and "S123456". An entry of "S123456" will match "S123456" and "SS123456".

I guess I'm not connecting the dots on how this will actually help. Would the calculation have to be a field in the cva_assets table or in the relationship. As I said, I'm very new to FM.

I could provide screenshots or a database file if that would help.

Thanks again!

[comment]

If I still follow this correctly, the calculation would be in the "hardware assets" table and it would be matching the serial# field in the "cva_assets" table. Thus, if you enter "123456" into the Serial# field of the "hardware assets" table, the related records in the "cva_assets" table will be those that have a serial# of "123456" or "S123456".

[AaronR]

Hmm.

I just created a field with calculation that adds an "S" to all serials. Looking at the relationship connection. There's no way to have a complex relationship that has an "OR". I also don't see a way of using a "contains". I callled the new field sserial.

This would work:

cva_assets::service_tag <contains> hardware_assets::serial

This would work:

hardware_assets::serial = cva_assets::service_tag

OR

hardware_assets::s_serial = cva_assets::service_tag

Since not every one is incorrect, I'm not sure how I'm supposed to handle this.

[comment]

See post #2.

[AaronR]

Here's an example of some of the serials for matching in each table. The S only gets added to some serial#'s. I think this is when they go through an automated scanning process at a certain distribution center. Parts that go through another center do not get the S. Only the last one in the sample is different.

SERIALS

hardware_assets cva_assets

W85042V0PP6 W85042V0PP6

SFTX0918E3LQ SFTX0918E3LQ

43235872309 43235872309

2CE9260DRS S2CE9260DRS

So, if I used what you propose in #2, then 3 out of 4 of the fields would no longer match. Essentially an inversion of the issue.

[comment]

Perhaps the attached can make it clearer:

MatchOR.zip

[AaronR]

Ahhh. I think I understand now. I didn't realize the List () calculation was actually creating a list of values, and even at that don't really understand how it truly equals unless it evaluates each item in the list for a potential match. It took me adding the calculation field onto the layout and seeing both value and Svalue in listed the field. When I tried the calculation, I only saw value listed.

Thanks for sticking with me. Truly appreciated!

[comment]

unless it evaluates each item in the list for a potential match

Yes, that's exactly what it does - see the note about multi-key field here:

http://www.filemaker.com/11help/html/relational.11.4.html#1027684