What is the best way to call a data from a list?

[zab]

Hello

Here's a beginner question.

What is the best way to call a data from a list?

I must estimate a time of production:

table 1: prod_data (number of linear feet)

table 2: variables (fields: ID, name, variable (time))

table 3: estimated time

What I must do is: prod_data*variable = estimated time

My problem is how to call (calculation) the variable from the list? I have never done that before. For example, I need the variable data from ID number 5.

At First, I created a field for every variables and just make a case calculation to retrieve it. But I don't think it's the best way. In another part of the database I will have to deal with many variables and I think making a simple list will be better. I just need to know how to interact with it

I hope I'm clear enough.

Thank you

Zab

[zab]

HI Again. No one have an idea how I can accomplish this? Please try again someone. I really need help.

The difficulty comes from the fact that the variable table is not related to another table. It is basicaly a variable that will be used in the calculation of the production time.

For example, in the prod_data table I have a project ID number. And I have 14 fields that gives the linear feet of different part of a product.

Example:

wall = 220 (linear feet)

Shapes= 74 (linear feet)

Truss = 2000 (linear feet) and so on

I need to multiply thoses numbers by the variable that is the time it take to build each part of that product

Example:

wall cutting: 2,5 (minutes)

Wall assembly: 20 (minutes)

truss: 2,5 (minutes) and so on.

The third table gives me the total estimated time of production for each part of the product

Wall assembly = 220*20

I know I can put those variables in the calculation script itself

I know I can create different fields and enter the variable one for each variables fields. It doesn't look good to me.

I need those variables to be editable and changed by the production manager that's why I want to put them in an independent table.

What I don't know, is how to include those variable into my calculation considering the table is not related.

Maybe I don't do this the right way. Maybe I need another table. I can't find the right way to calculate this. I'm open to any suggestion or keywords or example to follow. Maybe it's too easy and I don't see it.

Thank you for your help.

Zab

[Fitch]

It's not completely clear to me what you're envisioning, but if you want editable calculations you can set up a text field where you store/edit the calc, and then you can use the Evaluate function to get the result.

[comment]

Perhaps you should take a look at the demo file posted here:

http://fmforums.com/forum/showpost.php?post/309136/

Change Invoices to Projects, Products to Parts, and the Price fields to whatever you want to call your variable.

[zab]

I have made that calculation:

prod_estimation::wall * GetNthRecord ( variable::variable ; 2 )

It works in getting the field I need, but it's getting the second record and what I want is the record ID : 2

I have a made a simple DB to download:

http://angeleo.com/files/prod_estimation.fp7

IF you can tell me the right way to do this I'd really appreciate.

Thank you

Zab

[comment]

That doesn't look anywhere near like the file I have pointed to.

[zab]

I have done, in another project, something just like the file you have pointed to. But it's not what I need here. I really need a way to collect those variables records and I don't think a line item table is what I need. I can't use a portal. Or show me the right way to build it if you think it's the way to go.

The prod_estimation table will have a least a dozen of different fields ( and hundreds of records) and the variable can have hundreds of records that will be used in different tables' calculation. So I need a way to get just the record I need (from the varID) to insert in a calculation.

Any idea how to do this?

Edited March 5, 2009 by Guest

[comment]

I don't really know what you need - I can only go by your description above, which I understood should lead to something like:

ESTIMATE

Client: ABC

Project: XYZ

• Wall cutting: 220 feet * 2.5 min/ft = 550 minutes

• Wall assembly: 220 feet * 20 min/ft = 4,400 minutes

• Truss: 2,000 feet * 2.5 min/ft = 5,000 minutes

TOTAL = 9,950 minutes

To me, this is EXACTLY like an invoice, so if that's not what you want, perhaps you should elaborate.

[zab]

That's why I did the example. You see, the calculation (GetNthRecord ( variable::variable ; 1 )) made to retrieve the variable works but it's not good because it get the first 1 records when I need it to get the record ID #1. It's the same now but it may be different in a year when I try to retrieve ID # 58 that is located in the record number 56.

Can you give me another option to retrieve the variable than the one I used in the estimated_time table?

I need it to be done in the estimated time table because this way everything will be calculated automatically. I don't want a portal. I will need this table later on to compare with the real time of production. And get various stats from it.

If I make a line item. I will have to enter extra data in a kind of invoice table. I don't need that. It's an extra step that is not required.

Or maybe you're right and I need it but I just can't see how to do it?

[comment]

I'm sorry, I don't quite see what you're trying to build, in terms of the final result (your example doesn't really help with that). I think I can answer your original question, though: what is the best way to call a data from a list? Answer: a relationship based on the ID of the data you are calling.

Therefore, if you want to retrieve data from a record with ID 56 (in another table), you need to enter 56 into a field in THIS table, so that the relevant record becomes related.

Of course, if you want to retrieve data from more than one record, you will need more relationships and more fields to enter the "key values" into, in order to establish the links. That's why it's more efficient to have a line items type of table, where a single relationship retrieves each item's data into a dedicated record.

[zab]

Ok then if I can't call the variable using a table because I can't make a relation between the variables and the clientID. Then I suppose the best way to do this is by creating a global variable in a script. Something like : Set Variable[$$wall_cutting; Value:2] or Set Variable[$$wall_cutting; Value:prod_estimation::wall * 2]. I think it's the best way but them problem is nobody except me can change it if it's needed. Anyway I'll live with it.

So now I have to study the global variable and learn how to call it into a calculation... Any fast tip?

Thank you for your help. I really appreciate it. Maybe I'm not too clear but I'm quite sure a line item is not the right choice here.

[comment]

I am afraid I cannot help without knowing your purpose. You keep saying 'I cannot do it this way' - but you don't say what 'it' is. I even made a guess of what the final result should look like earlier, but you haven't said whether it's right or wrong.

[zab]

It's simple really

table 1: Job specific data (clientID, ft of wall, sq.ft of roof, nb of windows, ft of floor, etc... )

table 2: Variables (estimated time of prod for 1 ft or sq.ft or ... (wall cutting, wall assembly, roof cutting, roof assembly, floor assembly, window assembly etc.)

table 3: estimated time of production: fields from table 1 * fields from table 2 (ex: wall assembly = table1:wall * table2:wall_assembly : wall cutting= table1:wall * table2:wall_cutting )

At this point I have the total estimated time of production. (for 14 different aspects of building, walls, roofs etc.)

table 4: Real time of production = All men working time entered by job number and every different part of the work.

Then I will do one or two reports showing the difference between the estimated time (table 3) and real time of production (table 4). Get some stats about the time it take for different models etc. And see how far off (in %) we are from our estimated time table.

Then after enough projects, the estimated time of production should be re-evaluate and the variable table will be updated with more realistic time for production...

That's it.

That's why I was looking for a way to have this table2 (with 3 fields: ID, name, variable) updated at will. I could put the variable directly into the calculation but then it's impossible to update for the manager, or use the $variable in script or with Let calculation may be the better way, but again impossible to update for him. So I think I will simply make seperated fields that will be called in the calculation, so the manager can modify it without asking me. I did it before and it worked fine. It's just that in the next part of the project I will have to deal with many more variables and I thought it would be more efficient in a list (with 3 fields: ID, name, variable) than in 50 seperated fields for each individual variables. I can call individual fields in a calculation, but I can't find the way to call from an ID. In the file I did as an example, I tried to give an ID to the table2 and give the same ID in the estimated time of production table3. So they were related. But I can't find the way to call the variable I need through its ID, it work with the GetNthrecord but it's too dangerous. If a record is deleted, everything goes to hell. I was close but...

I tried to figure out a way but I can't see the relevance of a line item here. Because one variable in table 2 is applicable to only one field in table 3. ANd there is 14 fields in one project. Or there is another way to do it that I don't know. The relationship isn't clear. It can't be the client, maybe it's in between the part of the job (floor with floor assembly AND floor with floor cutting etc) It look like a mess to me. I really think it is simply a variable to be called in a calculation and it's not related to the jobID itself. Maybe it's way too much advanced stuff for me ;-(

I hope it's clearer that way. If not, you will have to learn to speak french and then you'll see I'm way better in explaining myself.

You're a charm anyway for trying to help me.

Bye

Isabelle

[comment]

IMHO, your table 3 *is* a line items table.

Once you enter the ID of wall assembly into the "ProductID" field, it should then lookup the "price" (what you call the variable) of wall assembly from the "Products" table (your table 2), based on matching the "ProductID", and multiply it by the wall "quantity" taken from the Job record*.

That's what seems to me the best solution for what you describe, unless I am missing something from your description.

---

(*) I would probably prefer to use yet another table to list the main elements of a job (e.g. walls, roof, etc.), and their quantities, but that's not the issue here.