Need Assistance with my new project

[rcm]

I am doing a new project and I am stuck with this for over a month, here is the scenario, Please help me.

I have a filtered portal, wherein a user will select lengths to be used for the calculation.

For the given data from excel: length, half length. 

I need to determine the combination of the user selected lengths to be used to minimize excess for the total lengths in the portal. Am I making any sense? hehe. Please the portal table below. I need it to be automatic. 

If I add the half lengths of the previous row or next row with the lengths. The calculation will be able to determine the lengths used and if they are connected via the half lengths. Thank you in advance.

Portal Table

[comment]

1 hour ago, rcm said:

Am I making any sense?

Not really.

If this is a problem of calculating something, please explain what is the input to the calculation and how would you calculate the result using pencil and paper.

However, it looks like you may be looking at a problem that cannot be solved by calculation - specifically, the cutting-stock problem. If that is the case, you may be better off looking for solutions outside of Filemaker.

See something similar here: 
https://fmforums.com/topic/105432-find-to-the-nearest-sum/?do=findComment&comment=477089&_rid=72594

 

[rcm]

Thank you for your response. Yeah that is something similar. It is a cutting stock related problem. based on the total of the half lengths of each portal to the User Selected lengths, it will automatically determine the best or closest user selected length with the total of half lengths. 

In the example table. the user selected 6000, 7500, 9000, 10500, 12000 - these are the options

Now in the portal B-1 has a length of 7000 and 3500 half length, so it is less than the highest length the user selected

then we can add the half length of B-2 to make it 10000, we can use now 10500 (user selected) but to check if we can use 12000, We'll try to add the half length of B-2 again, the total would be 13000. So, 12000 is not applicable only 10500. Then move to the next portal until the last

[rcm]

any more suggestions please?

[comment]

Sorry, I wasn't able to follow your explanation.

 

[rcm]

hmmmm. For example, A user selected these lengths 6000, 7500, 9000, 10500, 12000. These lengths will be used as a reference for cutting.  I need to get the best or closest user selected reference length to avoid wastage by summing up the length of all the labels and specify where to Cut. 

Example table

Label     Total Length      Half_Length     Reference Length    Cut

B1          7000                    3500                10,500                     NO

B2           6000                  3000                                                YES

B3         7500                    3750                10,500                       NO

B4         8000                     4000               9000                       NO

 

So for B1, the reference lengths that can be used are 7500, 9000, 10,500 and 12000. but for the best result we would like to use the maximum first to the lowest possible. So, we will add the half length of the second record B2 which is 3000

Total Length of B1 + Half Length of B2 = 10,000   by now, here we  can use reference lengths 10,500 and 12,000 

but we will try to add the half length of B2 which 3000 again to check if we can use 12,000. the total would be 13,000 which greater than 12,000 therefore 12,000 is not possible to use.  

So for record B1 up to the half of B2  , The closest reference length we can use is 10,500. which means at B1 there is no Cut and on B2 there is a Cut because we only use half of its total length.

Then from B2, the remaining half length, we will try to use it and add it to the next record which is B3, if we add 3000 (half of B2) + 3750 (Half of B3) = 6750 but we need to get the maximum length 

So, we will try to add the other half of B3 (3000) + 6750 = 10,500. Then try to add half of the next record B4 to check if we can use 12,000. 

10,500 + 4000 (half of B4) = 14,500 so it is greater than 12,000 which means it is not possible. 

Then on B4 we can use 9000 instead of 10,500 or 12,000 to have less wastage.

OR

Check all the lengths of all the records first by using the reference lengths combination, and determine where we can cut (in half) and what reference lengths to be used.

The order of records cannot be interchanged.                                             

Edited June 15, 2023 by rcm