Database queries

[AD_10841348]

Currently using, Prime 9.0.0.0.

In ANY version, is there a way to have a simple database query like return "weight" from "AISC Shapes.xlsx" where "Name" = "C10x15.3"? Or workaround?

Is it possible with the tables you can create in Mathcad? I can create the table if need be.

Thank you in advance.

[Werner_E]

---

@AD_10841348 wrote:

Currently using, Prime 9.0.0.0.

In ANY version, is there a way to have a simple database query like return "weight" from "AISC Shapes.xlsx" where "Name" = "C10x15.3"? Or workaround?

 Depends on what you consider being "simple"  😉

You may read in the table using the READEXCEL() function and then use lookup() or vlookup() to get the desired value.

-> https://support.ptc.com/help/mathcad/r9.0/en/#page/PTC_Mathcad_Help/lookup_functions.html

An alternative to using READEXCEL() is to embed the Excel sheet in the Prime file by using the Excel component. This makes the Prime file self-contained, but also larger and slower and you have to think about modifying the embedded sheet when the original Excel file is modified.

A more elaborate approach and much more work setting it up if the Excel database is larger would make usage of combo boxes as @terryhendicott  demonstrated in this thread https://community.ptc.com/t5/Mathcad/Excel-and-combobox/m-p/870154/highlight/true#M206266

[terryhendicott]

Hi,

Since the thread on large combo boxes have realized you can cut and paste large data from a Prime matrix into the combo box just not into the first column of the combo box.  A large matrix can be read into Prime by READEXCEL() copied into the combo box. Matrix then deleted leaving the combo box

What is returned when you select the entry in combo box is a column vector containing that row of the matrix.

If you upload the spreadsheet we can create the combo box.

Cheers

Terry

[Werner_E]

The database has 2000+ lines.

And copy and paste is implemented in Prime in such a silly way that only the visible part of a matrix is copied.
So it seems it would be necessary to enlarge the matrix to be displayed to its full size in Prime before copy and paste would work.
Thats quite uncomfortable and maybe modifying the xml as you described in an earlier thread is even easier?
Or am I missing something concerning copy&paste a large matrix?

Next problem are the units because Primes very basic implementation of the combo boxes does not allow for combined units like kg/m.
Two possible workarounds:

a) don't use units in the combo box and add them later
b) create a custom unit like

and use this in the combo box.

[terryhendicott]

Hi,

Take it back

[Werner_E]

---

@terryhendicott wrote:

Hi,

Take it back

---

??? Not sure what you mean?

[terryhendicott]

Hi 

left a message you could use CTRL A to select a large matrix.  You can but it selects the whole equation.  You can paste it into Prime but you cannot paste it into the combo box.

I simply want to take back this post

Agree with you there needs to be a way to select the whole matrix including hidden cols and rows 

Cheers

Terry.

[Werner_E]

I see.

Selecting of parts of a larger matrix manually is a "mission impossible" in Prime and scrolling a matrix is implemented in a very silly way - sure not useful for actual work.

And yes, copy&paste is implemented faulty and quite idiotic in Prime. Not only concerning matrices but also the copying of multiple regions. It only works with single regions and seems not to use the clipboard - it fails if the source is closed before trying to copy.

I am not sure if the reason is ignorance/incompetence of the developers or if its due to a faulty toolbox they are using which maybe is not updated to be used with current .NET updates.

So it seems that the only way at the time to implement the data in a combo box is to modify the xml file externally as you explained in an older thread. Not the "simple" way which was demanded by @AD_10841348  😞

Maybe a future version of Prime will allows the usage of a Prime matrix as input to a combo box ...

[Werner_E]

Here is the implementation of the most basic way (using READEXCEL() and lookup() as already described.
Its sure not very comfortable as you have to take great care when typing in the Name to use the proper case (C10x15.3 would throw an error because of the lower case "x") and avoid typos.

[AD_10841348]

Thank you Werner_E, I think I will be going with the last option.

However, I would like to make it a bit more automated. I'm trying to do something like the following: Create a function with two inputs (member and property) and returning property_value. I'm getting stuck with the last line of code. When I try to call the lookup function with the property_index variable instead of manually entering "4", it gives me "it's not an integer" error. I can't figure out how to change the property_index variable to an integer. Any ideas?

[AD_10841348 · Accepted solution]

I figured it out: For anyone else who's interested. Thank you everyone who replied.

[Werner_E]

The result of lookup is a nx1 matrix (in your case usually a 1x1 matrix) so you should use the vector index 0 (or ORIGIN) to get the value itself.

You can turn the calculation into a utility function and add the unit as an additional argument - unfortunately we can't read it automatically from the first sheet in the Excel file.