Matching Values in separate Repeating Fields

[Corey-Troy]

I am using FM. v8.

Is there a formula to find any excact matching values in a [field A] with a value in another [field B]?

Both fields are repeating fields.

The 1st having [field A]25 rep's,

and the 2nd having [field B]7 rep's.

I use a valuelist to display in [field B] all the values of [field A], but once a MATCHING VALUE is used in [field B], then that particular value to NOT be displayed in the valuelist anymore.

I need a formula to find any EXACTLY MATCHING values in any of the values in [field A] that are found in [field B].

I can create a 3rd field [field C] and here i would like displayed any values that are NOT matched in [field B].

Is this possible???

OR Alternately,

Is there a way to, when a MATCHING VALUE in [field B] is display from the valuelist from [field A] values, to DELETE that value from the [field A] ?

Any value from [field A] could be used in ANY repetition of [field B].

IE. The Value in [field A]-repetition(1) could be used in [field B]-Repetition(5), so i cannot only refer to each repetition by what repetition they are.

EG.

[field A. Rep 1] value = 100

[field A. Rep 2] value = 50

[field A. Rep 3] value = 25

[field A. Rep 4] value = 10

[field A. Rep 5] value = 85

[field B. Rep 1] value = 25

[field B. Rep 1] value = 10

[field B. Rep 1] value = 85

[field B. Rep 1] value = 0

[field B. Rep 1] value = 0

In this instance the Values selected in [field B] 25,10& 85 would now be NO Longer available, or alternately deleted from the [field A] values.

How can i accomplish this, or what is a work around?

I cannot NOT use repeating fields.

ctm.

[comment]

I suppose it's possible, but without version 8.5 or 8 Advanced it's going to be tedious. You need to define a calculation field (stored, result is Text) along the lines of:

Let ( [

usedValues =

ValuesTarget[1] & ¶ &

ValuesTarget[2] & ¶ &

ValuesTarget[3] & ¶ &

...

ValuesTarget[6] & ¶ &

ValuesTarget[7]

] ;

Case ( IsEmpty ( FilterValues ( ValuesSource[1] ; usedValues ) ) ; ValuesSource[1] & ¶ ) &

Case ( IsEmpty ( FilterValues ( ValuesSource[2] ; usedValues ) ) ; ValuesSource[2] & ¶ ) &

Case ( IsEmpty ( FilterValues ( ValuesSource[3] ; usedValues ) ) ; ValuesSource[3] & ¶ ) &

..

Case ( IsEmpty ( FilterValues ( ValuesSource[24] ; usedValues ) ) ; ValuesSource[24] & ¶ ) &

Case ( IsEmpty ( FilterValues ( ValuesSource[25] ; usedValues ) ) ; ValuesSource[25] )

)

where ValuesSource is your Field A and ValuesTarget is your Field B. Then use this new calculation field as the source for your value list. There's probably a better way to do whatever you're doing - but you haven't told us what that is.

[Corey-Troy]

Thank you for the time to reply to my problem.

I an trying what suggested and ended up with:

Let ( [usedValues = Outgoing::Length[1] & ¶ & Outgoing::Length[2] & ¶ & Outgoing::Length[3] & ¶ & Outgoing::Length[4] & ¶ & Outgoing::Length[5] & ¶ & Outgoing::Length[6] & ¶ & Outgoing::Length[7] ] ;

Case ( IsEmpty ( FilterValues ( Length[1] ; usedValues ) ) ; Length[1] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[2] ; usedValues ) ) ; Length[2] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[3] ; usedValues ) ) ; Length[3] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[4] ; usedValues ) ) ; Length[4] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[5] ; usedValues ) ) ; Length[5] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[6] ; usedValues ) ) ; Length[6] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[7] ; usedValues ) ) ; Length[7] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[8] ; usedValues ) ) ; Length[8] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[9] ; usedValues ) ) ; Length[9] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[10] ; usedValues ) ) ; Length[10] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[11] ; usedValues ) ) ; Length[11] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[12] ; usedValues ) ) ; Length[12] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[13] ; usedValues ) ) ; Length[13] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[14] ; usedValues ) ) ; Length[14] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[15] ; usedValues ) ) ; Length[15] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[16] ; usedValues ) ) ; Length[16] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[17] ; usedValues ) ) ; Length[17] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[18] ; usedValues ) ) ; Length[18] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[19] ; usedValues ) ) ; Length[19] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[20] ; usedValues ) ) ; Length[20] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[21] ; usedValues ) ) ; Length[21] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[22] ; usedValues ) ) ; Length[22] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[23] ; usedValues ) ) ; Length[23] & ¶ ) & Case ( IsEmpty ( FilterValues ( Length[24] ; usedValues ) ) ; Length[24] & ¶ ) &

Case ( IsEmpty ( FilterValues ( Length[25] ; usedValues ) ) ; Length[25] ))

All works as required for 1 record in table B, BUT if i create a 2nd record in table B, the remaining values are the ONLY ones that are in the valuelist, BUT then after selecting a value that value does NOT then dissapear from the valuelist.

IE. Works for ONLY 1 record.

I tried changing the CAl to a Global, but it would not have any used values dissapear.

Any idea's???

[comment]

Your original question did not mention that the two fields were in separate tables.

I think you better explain what this is REALLY about: what are your tables/fields, how are they related, what are you trying to accomplish with this arrangement - and why you think you MUST using repeating fields.

[Corey-Troy]

table A

It is a 1 page layout i print out for lengths of conveyor belting to be placed on as it is inspected.

1 roll of belt may have several (20) different sections between joins or events of various types.

Then in table B

The rolls in various sizes need to be joined together into a required length(say 200m).

It could be made up by a few different table A layout sheets, with a couple of lengths from 1 record in table A, and a couple from another record in table A.

The problem i am having is I need to keep track of ALL lengths in table A whether used in table B or not.

YET, ONLY want to be able to use each separate length from Table A ONCE, as phyically it can be only used once, then it is sent back to site.

Sorry for explaining it well.

I didi not want to reveal too much info as i did not want to put more confusion into my post.

table A i have 25 reps in numerous fields(length,width,type...)

But the main concern is the length.

At the bottom of the page i have a summary field to give a total length of that roll for that record. (It tally's once the handwritten data is transfered into fmp.

Corey....

[comment]

Sorry, you have lost me completely. It sounds similar to the attached file - but this one uses proper relational structure, not repeating fields.

PortalToPortal.fp7.zip

[Corey-Troy]

Think the sample portal file may be something like what i need.

I have attached the file i use.

The despatch sheet is where i select a rollwith various lengths from the Inspection Sheet.

If you click the field labelled (Roll #) on the Despatch sheet, it will ONLY show rolls that are for that Costomer and Belt TYPE.

The field below is where i select a length from from the above field roll.

This is where i need to have the length NOT AVAILABLE to be shown once it has been used.

I am not really that confident with relationships and portals and have thus far been able to bluff myself to create this file that does the job, BUT not very effectively to date.

It just needs some refining.

I cannot see how i can avoid repeating fields, but not being an expert, i am very open to alternate senario's.

Corey....

[comment]

I don't really understand what your file is supposed to do, so I can't say if it needs refining or from-the-ground redesign - though I suspect it's the latter.

I'd suggest you learn more about relationships and relational database structure. You could take my file as a starting point: the Software table is the Table A in your example, the Computers table is your Table B, and the Installations table is the one that your file should have instead of the repeating fields in Table B (I think).

The portal-to-portal display is just that: a user interface device. What drives it is the underlying data structure. You could change the layout so that user selects the software to be installed from a value list instead*. But that too requires a solid design under the hood.

---

(*) I believe you could find some examples of that if you search for "dwindling value list" here on the forum.

[Corey-Troy]

To explain the file.

The table(Inspections) has data added that are actual separate lengths of belt that make up 1 roll.

1 roll of inspected belt can be from about 20mtrs - 300mtrs long, but have several separate lengths to make up the combined length. It may have joins or rips or clip joints(temporary fasteners) etc.

All this data is placed into the inspection layout.

Then according to what the customers needs are, say 250mtr roll to be sent back.

It may be neccessary to remove sections of belt in the middle due to damage and so forth.

It may be neccessary also to use ALL a roll inspected or bits and pieces of a few separate rolls inspected so long as the length in toal once completed is the required 250mtrs.

Hence inspected roll ID valuelist on the despatch sheet.

The Despatch layout is where once the customer and belt type are placed in the sheet, ONLY that particular customers inspected roll numbers(id) are shown in the top valuelist(inspected roll ID).

Then the length from that roll ID is selected to be utilized in the despatched roll, this continues until the required despatch roll length is achieved.

It is the valuelist in my sample file that shows the roll ID & length that i need to adapt to ONLY show the values that are NOT used in any of the despatch records.

Hope this clears it up.

You formula worked perfectly for 1 record.

Is there not a way to make it work across all records.

The sample file you posted ONLY seems to work also for 1 - 1 record.

I need to have a value that is used from a certain inspected roll ID to never be able to be listed in a valuelist again, as i physically is now gone back to site and not in stock anymore.

Corey....

[comment]

I am still missing pieces of the puzzle, for example - what happens the next time a belt comes in? Do you use the previous record as the basis for the current job, or do you start all over again?

In any case, if you would modify my example by adding a table, say SoftwareItems (related to Software by SoftwareID), then you could select the SoftwareID to install from, and show only related SoftwareItems in the source portal.

It looks like you are assembling a new product from the parts of one or more existing products. If so, a Part can only belong to one Product at any time, so you don't need the join table (Installations in my example). You could just shuffle parts from product to product by changing the part's ProductID - see attached example.

Note that in real life, there would be probably be a Job table, and Products would belong to a Job. That way the From portal could automatically show all parts from the Job's products, instead of user having to select them manually.

PortalToPortal2.fp7.zip

[Corey-Troy]

I am still missing pieces of the puzzle, for example - what happens the next time a belt comes in? Do you use the previous record as the basis for the current job, or do you start all over again?.

Each new roll that comes in will require a new record sheet printed out for it.

I did think originally about keeping all these sheets into only 1 table, but the problem is there could be several records required for the Inspection sheet, for only 1 record for the despatch sheet.

It all depends on how much belt is on each roll when it comes in.

[Corey-Troy]

To try to simplify what i am doing.

Imagine this in a PC Store:

1. You have a (tableA) layout for PC Parts.

It has the fields:

[inspection ID] - Unique ID for each record

[customer] - growing valuelist for customer names

[type] - part types

[amount] - part amounts

[total parts] - summary of total parts supplied

2. You have a (table layout for PC Complete systems.

It has the fields:

[depstach ID] - unique ID for ech record(not same as tableA)

[customer] - Valuelist looking at entries from table A

[type] - type of system customer wants returned

[inspection ID] - valuelist of related(customer + type: tableA) table A record inspection iD's

[type] - valuelist from table A of the type of parts supplied

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Customers give you a box of PC components(ram, motherboards, hdd, processors etc) all disassembled in that box.

The customer(say customer X) wants you to give them a Desktop PC Complete ready for use.

So, you proceed to create a new record [inspection ID 001]in table A (for Customer X)to list all the individual components in the the box they supplied according to their types and the amount of each component.

You realise from the inspections that there are many different parts, but NOT enough to complete a Desktop PC as the costomer wants returned. ((There may be 3 hdd, but no case etc.....))

So you ring the costomer (customer X) and notify them of the results, so they send another Box of parts to you.

So you create a New Record in the Table A for this box of parts. (Now you have enough parts).

((This new record will be [inspection ID 002]))

When completed, you go to the table B layout and create a new record for the customer(customer X).

You proceed to enter the customer name(customer X) and the type of system(Desktop).

Then using the 1st valuelist [inspection ID::tableA] you select the record [inspection ID 001].

Then using the 2nd value list under the 1st, you proceed to see a list of the parts listed on the table A Inspection ID for record [001], so you select the part you need.(7 repetitions for each valuelist).

Select the ID & Part until there is no more parts required from [iD 001].

Then requiring still more parts(not in [iD 001], you go to the next blank repetition of the 1st valuelist and select the [inspection ID 002] record, then with the valuelist under that valuelist you select the required part(2 x Valuelists One above the other... [inspection ID::tableA] & [type::tableA]).

Continuing on until enough parts are gained to complete the system.

The Desktop PC once completed is then given back to the customer.

! NOTE:

When a PART(type) is selected in the depatch sheet(tableB) from a valuelist, that part(type) because it is being used CANNOT be Used AGAIN as it is no longer available for any other Desktop PC's for that customer.

EG. Say customer X supplied you in boxes 1& 2 a total of only 1 Case.

You used that case in the 1st valuelist of (types)in table B, so when customer X comes back in 12mnths time and gives you more parts in a box to make another Desktop PC, AND no case was supplied, i want the CASE to NOT be LISTED in ANY records in table A for customer X, as it WAS USED in a previous table B record.

So it is with ALL USED parts(types).

This is basically what i do with belt instead of PC parts.

I nned to give the employees a 1 page sheet for them to manually list ALL bits and pieces in the box of parts.

Then i enter this into Filemaker Pro.

Then when i know what the customer wants returned, i create a table B record an list all the parts i need from each individual record(box) from tableA.

I then give this back to the employees to show them what parts they need to put together to make up the Desktop PC for the customer.

Hope i have put what i need into a more understandable senario of what i do.

My AIM is:

To REMOVE ALL Parts that have been USED(Displayed/Shown/Used/Utilized) in Table B from the Valuelist to prevent it being accidently listed to be used AGAIN. (It is NO LONGER AVAILABLE as it was used in a previous PC).

If there is a way to avoid using repeating fields i am open to suggestions but i cannot see how, though i am far from fluent with Filemaker Pro.

Corey....

[comment]

I think I understand now why I don't understand you. You describe everything from the point of view of the forms that need to be filled. Your solution is built around these forms.

My approach to your example would be to build a solution that tracks my business. I have customers? I need a table to track Customers. Customers give me boxes? I need a table for those Boxes. (You can call this table Inspections, but the fact that a box gets inspected - and a form is filled while inspecting a box - is only a minor aspect of a box. The fact that a box was delivered, and that the box's contents are now in my shop is much more important.) These boxes contain individual parts? That means another table. Now I need to create a product out of the parts in the boxes - so I will need a table of Products. And since the product is going to be built out of the parts in some SPECIFIC boxes, I will need a relationship between the product and its boxes (and by extension to the parts in those boxes).

So before I start thinking about layouts and forms, I will have STRUCTURE in place, a structure that will allow me to look at my data from any point of view I need. It could look something like the attached picture. Note that Parts belong to a Box, Boxes belong to a Product, and Products belong to a Customer. This means that a Product can "see" the parts in its own boxes.

Now comes the question how to handle the parts that are moved out of their box and into the product. The easiest way would be probably to do the same in the database: the script would clear the BoxID field of a Part, and fill its ProductID field with the ID of the parent product. As a result, the Part will no longer belong to a Box, but rather to the Product directly (the red dotted line). And if a part is no longer in its box, it cannot be taken out of the box a second time - which is what you want.

Note that in Filemaker's relationships graph you will need another occurence of the Parts table for the parts used in the product.

[Corey-Troy]

Thank you for the time to reply to my problem.

To clarify i have a attached a picture of what i would need according to the picture you posted.

Is that what you meant?

Corey....

[comment]

I don't think so. The rectangular boxes represent TABLES. "Inspected RollID's" and "Despatched Roll ID" sound like FIELDS.

If I understand correctly, my "Boxes" are currently your "Inspections", my "Parts" are your "Lengths", and my Products are your "Dispatched Sheets". But hey, it was your example I used, so you should be able to map it back to your stuff.

[emjay]

Regarding your example: PortalToPortal.fp7.zip

Thank you for including this example. It was very helpful to me in my current project.

One question - do you know why the "add" script doesn't work with Instant Web Publishing?

I'm trying, I'm trying, but I can't seem to figure it out.

Thanks.

MJ

Edited April 30, 2007 by Guest

[comment]

I know very little about IWP, so this is a mere guess:

Try selecting the Commit Records step, and turn on the 'Perform without dialog' option.

[emjay]

Thanks for your quick response.

Sadly, that wasn't it.

What's curious is the port2port example you gave on a different thread works.

Thanks again, though.

MJ

[emjay]

One more question about the portal to portal example - (thanks again it's been really helpful)

Using your example - let's say the Uninstalled Software portal has many more records that can be seen without scrolling. So if I select one of the records I have to scroll to, how do I get back to where in the list I left off without having to scroll down from the top again.

Thanks.

Mj

BTW - the IWP issue was the Freeze Window step

[comment]

One way would be to start by setting a variable to Get(PortalRowNumber), and ending with:

Go to Portal Row [by calculation; $portalRow]

Note that the demo is meant to show just the basic structure, using a minimal user interface. There's no limit on how far you can go with building a different kind of interface.

[emjay]

Thanks for you quick response -- I'm trying something like your suggest.

The step I'm missing is this: after I go back to the original layout and commit records it seems to go to the wrong portal. I don't see the step that allows me to choose the correct portal on the page.

Thanks again.

[comment]

A simple solution: select the portal you want, and send it to back.

Alternatively, go first to a field in the portal you want, then to the row. Or name the portal and use Go to Object [] before going to the row.

[emjay]

Thanks for the tips.